Schedule and Run a SSIS package

• SQL Server 2005 is bit different from SQL Server 2000. In SQL Server 2000, you can create the job and run it without problem. In SQL Server 2005, we need to follow some steps for creating job.
• The job creator account should have the roles of Sysadmin, SQLAgentuser, SQLAgentReader, SQLAgentOperator and job needs to be run under proxy account
• The steps are follows as below.
• Creating Account:
  SQL Server Management Studio- >Highlight Security->New Login->Give login user and password and database is your selected database
• Server roles: check sysadmin
  User mapping: your database
  Msdb database:Iinclude SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole -> click on OK.
• Creating Proxy Account:
• In Object Explorer, expand a server.
• Expand SQL Server Agent.
• Right-click Proxies and select New Proxy.
• On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before you create a proxy if one is not already available. For more information about creating a credentialCheck the appropriate subsystem for this proxy.
  On the Principals page, add or remove logins or roles to grant or remove access to the proxy account
• Creating Credentials:
• In Object Explorer, expand Security, right-click Credentials, and then click New Credential.
• In the New Credentials dialog box, in the Credential Name box, type a name for the credential.
• In the Identity box, type the name of the account used for outgoing connections (when leaving the context of SQL Server). Typically, this will be a Windows user account. But the identity can be an account of another type.
• In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.
  Click OK.
• Job Creation,Scheduling,Running:
• In SQL Server Management Studio-> SQL Server Agent -> Start. Select job ->New Job>give name
• Then select steps tab-> New Step->give name
• Type: SQL Server Integration Service Package->Run asmyProxy->Package source: File System.
• Select your file through browse->YourPackage.dtsx->Say OK->then schedule and run it

Choosing a SQL Server Authentication Mode

• Microsoft SQL Server offers administrators two choices of performing user authentication: Windows authentication mode and mixed authentication mode. Making the proper choice affects both the security and maintenance of your organization’s databases
• SQL Server Authentication ModesSQL Server 2008 offers two authentication mode options:
• Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
• Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server.
• Selecting an Authentication Mode Microsoft’s best practice recommendation is that you use Windows authentication mode whenever possible. The main benefit is that the use of this mode allows you to centralize account administration for your entire enterprise in a single place: Active Directory. This dramatically reduces the chances of error or oversight. For example, consider the scenario where a trusted database administrator leaves your organization on unfriendly terms. If you use Windows authentication mode, revoking that user’s access takes place automatically when you disable or remove the DBA’s Active Directory account. If you use mixed authentication mode, you not only need to disable the DBA’s Windows account, but you also need to comb through the local user listings on each database server to ensure that no local accounts exist where the DBA may know the password. That’s a lot of work! In conclusion, I suggest that you heed Microsoft’s advice and implement Windows authentication mode for your SQL Server databases whenever possible.

sp_MSforeachtable -Nice feature of sql server

• sp_MSforeachtable is a predefined stored procedure used to perform common action in all table of database at same time.
• For Example:Traditionally if you wanted to run a DBCC CHECKTABLE on every table in a database you'd have to write an elaborate cursor with couple of lines of sql script.
• Using the question mark as a place holder for all table names, the procedure will do the common task in a single line query which actually takes multiple lines in traditional way.
• Example Code : sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"
• You can provide up to 3 commands to the stored procedure using @command1 through @command3
• enjoy the fun programming...