Scheduling Packages in SSIS

Now you have your packages deployed and are ready to set up a schedule to run them on a regular basis. The typical tool used is the SQL Server Agent. Although you can use other third-party tools instead of the SQL Server Agent, discussing them is beyond the scope of this Tutorial.

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free "SSIS Training" Demo!

Scheduling SSIS Packages

SQL Server Agent

The primary way to schedule packages in SSIS is with SQL Server Agent, which ships with the SQL Server database engine. If you don’t have a database engine in your environment, then you must use something like Task Scheduler, which ships with Windows. Scheduling a package with SQL Server Agent is much simpler and gives you much more flexibility.

The first step to scheduling a package is to connect to the database engine. Ensure that the SQL Server Agent service is started. Right-click Jobs under the SQL Server Agent tree and select New Job. The New Job dialog will open.

On the General page, name your job Execute Package. On the Steps page, click New, which opens the New Job Step dialog. Type Execute Sample Package for the Step Name property in the General page, as shown in the below screenshot. Then, select SQL Server Integration Services Package as the type of step. For the time being, use the default SQL Agent Service Account as the Run As account. This means that the account that starts SQL Server Agent will execute the package, and sources and destinations in your package will use Windows Authentication with that account if they are set up to do so.

For the Package Source, select the SSIS catalog and point to a valid SSIS server. Pick any test package that won’t have an impact on production by clicking the ellipsis button. When you click the ellipsis button, you’ll see all the folders in the SSIS catalog. You still have the older options of File System, SSIS Package Store, and SQL Server for those using the package deployment model.
Under Package, Source is the SSIS Package Store. This is the older location, and it is where you would find the packages that you may have deployed to the MSDB or file system.

If you select the SSIS Package Store and choose a package there, the rest of the options are identical to those shown earlier in DTExecUI.exe, with the exception of the Reporting tab because there is no console to report to from a job. You can also optionally go to the Advanced page to set the Include Step Output in History option to get more information about the job when it succeeds or fails. Click OK to go back to the New Job dialog. You can then go to the Schedules page to configure when you want the job to run. Click OK again to go back to the main Management Studio interface.

After you have the SSIS catalog selected and a package selected, you can click the Configurations tab and make changes to any parameters or select an environment. You can also make changes to any Connection Managers by selecting the Connection Managers tab. The Advanced tab allows you to add any other package properties, but these should have been handled with parameters during development. If you need to run the package in 32-bit mode, there is an option for that also.

Frequently Asked SSIS Interview Questions & Answers

With the job now scheduled, right-click the newly created job and select Start Job at Step. A status box will open that starts the job. When you see a success message, it does not mean the job passed or failed; it just means that the job was started successfully. You can right-click the job and select View History to see if it was successful. This opens the Log File Viewer, which shows you each execution of the package. You can drill into each execution to see more details about the steps in the job. The information this step gives you is adequate to help you identify a problem, but you may need package logs to truly diagnose the problem.

Proxy Accounts

A classic problem in SSIS and DTS is that a package may work in the design environment but not work once scheduled. Typically, this is because you have connections that use Windows Authentication. At design time, the package uses your credentials, and when you schedule the package, it uses the SQL Server Agent service account by default. This account may not have access to a file share or database server that is necessary to successfully run the package. Proxy accounts in SQL Server enable you to circumvent this problem.

With a proxy account, you can assign a job to use any account other than the SQL Server Agent account. Creating a proxy account is a two-step process. First, you must create a credential that allows a user to use an Active Directory account that is not his or her own, and then you specify how that account may be used.

To create a credential, open Management Studio and right-click Credentials under the Security tree, and select New Credential. For this example, you’ll create a credential called AdminAccess (see Below screenshot). The credential will allow users to temporarily gain administrator access. For the Identity property, type the name of an administrator account or an account with higher rights. Lastly, type the password for the Windows account, and click OK.

NOTE: As you can imagine, because you’re typing a password here, be aware of your company’s password expiration policies. Credential accounts should be treated like service accounts.

The next step is to specify how the credential can be used. Under the SQL Server Agent tree, right-click Proxies and select New Proxy, which opens the New Proxy Account dialog (shown in below screen shot). Type Admin Access Proxy for the Proxy Name property, and AdminAccess as the Credential Name. Check SQL Server Integration Services Package for the subsystem type allowed to use this proxy.

Optionally, you can go to the Principals page in the New Proxy Account dialog to specify which roles or accounts can use your proxy from SSIS. You can explicitly grant server roles, specific logins, or members of given MSDB roles rights to your proxy. Click Add to grant rights to the proxy one at a time.

Click OK to save the proxy. Now if you create a new SSIS job step as shown earlier, you’ll be able to use the new proxy by selecting the Admin Access Proxy from the Run As dropdown box. Any connections that use Windows Authentication will then use the proxy account instead of the standard account.

List of Related Microsoft Certification Courses: