Scheduling SSIS Packages with SQL Server Agent

In our last post in the SSIS series, we discussed the deployment of the SSIS Package on to the server and various ways to execute the SSIS Package. In this post we are going to talk about scheduling the SSIS Package as a Job using the SQL Server Agent.

Starting the SQL Server Agent

In order for the job to run successfully, the SQL Server agent should be running on the target machine. For our demo purpose, let us consider our local machine as the target machine. Make sure that the SQL Server Agent is started on the target machine. You can start the Agent from a number of places – for this blog post, let’s use SSMS to do it. In order to start the SQL Server Agent, fire up the SQL Server Management Studio, connect to the local machine and navigate to the SQL Server Agent Folder. Then right click on the Sql Server Agent and click Start. The image below shows how to do this:

In this post we will schedule the package we deployed in our last post. I am placing the project folder in C:\SSIS\Packages location on the hard drive. The project name as well as the Folder Name is TextFileImport. Open up the solution for this Project. The image below shows the SSIS Package:

There is a small change we need to make to the package to schedule this as a job and run it successfully. There are various reasons due to which the SSIS Package might not run when scheduled as a job. This is well documented by MSFT in the KB article over here. Before scheduling the SSIS Package as a job let us have a brief look at the security which the Integration Services has on offer for us. Right click on the Data Flow task and click on the Properties in the popup menu. A mini window of SSIS Packages will open on the right. Scroll down to the bottom of the window and locate the Security Properties. The image below shows the SSIS Security Properties of the SSIS Package:

Clicking on the dropdown list for the Protection Level Property displays the various options available for the package security which are shown in the image below:

a) DontSaveSensitive:
This option does not encrypt the SSIS package but removes all the sensitive data from the package like passwords thus making the sensitive information unavailable for the other users. If other users try to open the package, the sensitive information is replaced by blanks and that information has to be provided by the user.
b) EncryptSensitiveWithUserKey:
This is the default option provided by the SSIS package. This option encrypts the sensitive data with the user key which is based on the profile of the user who created the package. The other users can open up the package but should change the sensitive information and save it. So when other users try execute the package the package execution fails.
c) EncryptSensitiveWithPassword:
This option encrypts the sensitive information by using the password provided in the Password field in the Security Properties of the SSIS Package. When other users want open the package, they should provide the password and then replace the sensitive data with the new values. If the user tries to execute the package without providing the password then the package execution fails.
d) EncryptAllWithPassword:
This option encrypts the whole package with the password provided in the Password field of the Security Property. The package can be opened or executed by providing the password without which the package execution fails.
e) EncryptAllWithUserKey:
This option encrypts the entire package with the user key which is based on the profile of the user who created the package. The user who created and saved the package using this option can only access the package and execute it successfully.

f) ServerStorage:
This option is supported only when the package is stored in the msdb database. This is not supported when the package is saved to the file system from the BIDS. When this option is specified, the entire package is saved using the SQL Server database roles.

Let us explore the default Protection Level “EncryptSensitiveWithUserKey” with help of an example. As mentioned earlier, this is the default protection level in the package. Deploy the package on the server.

Scheduling the SSIS Package as a Job

Scheduling SSIS Package as a job is very similar to that of scheduling of any other job. Fire up the SQL Server Management Studio by navigating to:

Start —> All Programs —> Microsoft SQL Server 2005 —> Sql Server Management Studio

Connect to the target instance of the SQL Server. In this example I am connecting to my development server on the network to play with the EncryptSensitiveWithUserKey Option. Navigate to the SQL Server Agent sub tree. Right click on the Jobs Folder and click New Job:

This opens the General Page of the New Job Editor. Fill out the Job name and the Job Owner Fields. For this demo let us make sa login account as the owner of this job:

Our next step is to create a new job step. To do this click on the Steps page on the left side of the New Job window:

Click on New button at the bottom of the page to create the new job step. This will open up the New Job Step Page as shown in the image below:

In the Step Name Field, fill out the name of the step. In the Type drop down list, select the SQL Server Integration Services Package option since we are running an integration services package. This is the only difference between scheduling the SSIS Package as a Job and a regular job is the selection of the type of job in the Type field. Since we deployed the SSIS Package on the server, select SQL Server in the Pacakage Source drop down Field. Provide the authentication details and select the pacakge which is intended to be scheduled to run on a regular basis. If this is the last step in the job, then go to the Advanced Page of the New Job Step:

In the On Success Action drop down list select the Quit the job Reporting Success option and click ok. This will take us back to the New Job Editor. If there are addition job steps, we can go ahead and create new job steps in the same way. Our next step is to create a schedule to run this job regularly. To do this click on the schedules page of the New Job Editor. The Editor is as shown in the image below:

To create a new schedule click on the New button or to pick an existing schedule click on Pick. In our demo, we are going to create a new schedule. Click New which brings up the New Schedule Editor as shown in the image below:

Enter the required details of the schedule. In this figure, we are scheduling the job to execute every day at 12 AM in the morning. Click on OK button twice to finish the creation and scheduling the SSIS Package as a Job.

Manual Execution of the SSIS Package Job

To Execute the SSIS Package as a job manually, navigate to the Jobs folder in the SQL Server Management Studio and find the job, ImportTextFile in our case. Right click on the job and click Run as shown in the image below:

We will observe that the job fails. To view the job log, right click on the job and click on the View History. This is will open up the job history/log as shown in the image below. Scrolling to the Job Steps reveals why the job failed:

The error message was:
Message
Executed as user: PARTTEST\lirfdev. …n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:33:43 PM Error: 2008-09-15 16:33:43.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node “DTS:Password” with error 0x8009000B “Key not valid for use in specified state.”. You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-09-15 16:34:32.57 Code: 0xC0202009 Source: TextFileImport Connection manager “DecipherDestination” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Description: “Login timeout expired”. An OLE DB record is available. Source: “Microsoft SQL Native Client” Hresult: 0x80004005 Descrip… The package execution fa… The step failed.

The error description clearly states that the SSIS engine failed to decrypt the Password. This is because the SSIS package was created with the Protection Level EncryptSensitiveWithUserKey. As explained earlier in the post, making use of this Protection Level option encrypts the Package with the profile of the user which created and saved the pacakage. When the job is executed, it is run under the profile of the job owner i.e. sa (whom we made the owner). Since the job owner profile and the SSIS Package creator User Key does not match, the SSIS Engine throws the error stating that the Password decryption failed and we are not authorized to access this information.

To avoid this, change the Protection Level option in the SSIS Package to DontSaveSensitive which does not save the sensitive information of the Package. Deploy the Package to the target server and change the sensitive information in the configuration files. Then execute the job and it will run successfully this time.

In this post we discussed the various Protection Level Options provided by SSIS and also how to set those levels. We then covered how these Protection Levels effect the Execution of the Job. And then we went ahead and scheduled the SSIS Package as a job to run on a daily basis. In future posts in this Series, we will look into debugging SSIS packages. Watch this space…