Systems Engineering and RDBMS

Scheduling SSIS Packages with SQL Server Agent

Posted by decipherinfosys on September 17, 2008

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…

About these ads

32 Responses to “Scheduling SSIS Packages with SQL Server Agent”

  1. Sue Massey said

    Hello. I was reading someone elses blog and saw you on their blogroll. Would you be interested in exchanging blog roll links? If so, feel free to email me.

    Thanks.

  2. janet said

    Thank you…… great help!

  3. Alan Horsman said

    Thanks for the great article. For the longest time, I couldn’t figure out how it would execute in BIDS and not under SQL Server Agent.

    I do have a follow up question though. At the end of the article, you suggest to change the Protection Level to “DontSaveSensitive”. How will the necessary authentication be achieved when using this protection level while using SQL Server Agent? I noticed that there is a reference to: “…and change the sensitive information in the configuration files”. Can you elaborate on the information that would be left blank, and where the configuration files are that need to be changed (name/extension would be great too).

    Thanks!!

  4. Greg Lechkun said

    I too am confused by your post with the Protection Level to “DontSaveSensitive”. It is not clear as to how you configure for this setting.

    I did use the Protection Level to “EncryptSensitiveWithPassword”, and found that despite entering the password for the package and deploying it using the password, I still get an error when executing it in SQL Agent; the error looks like:

    …SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER…

    The error seems to be related to the Oracle connection I’m trying to established.

    By-the-way; manually running the package from SSIS\MSDB, it doesn’t throw an error.

    • Rick MacDonald said

      Greg,

      I’m having a simular issue. What did you eventually do to solve this problem?

    • jbrimble@inovo.co.za said

      I too have changed the protection level to ‘Don’tSaveSensitive’ and am able to successfully run the package from SSIS/MSDB but I am having an issue executing this from the SQL Server Agent. It keeps throwing an error message saying that it cannot establish a connection when I am connecting via Windows Authentication.

      By the way, how do you change the sensitive information in the configuration files and where can these be found?

  5. Samp said

    Thanks for a gr8 article. This help me lot
    Thank u very much

  6. Monika said

    Hey Thanks for sharing such a great article.

  7. Yurk said

    Muchas pero muchisimas gracias, si no fuera por tu post no se que hubiera hecho… todo lo que necesitaba saber, por ahora, lo he aprendido gracias a ti … Idolo!!!
    Saludos desde Perú … (3)

  8. Myles said

    Thank you for this post. It made my job a WHOLE LOT simpler! (I just wanna deploy the thing!!!)

    Thank you,
    Myles

  9. David said

    Good article, keep on going!

  10. James said

    Microsoft should explain things this way!! Thank you.

  11. Sanj said

    Very helpful article! Thank you.

  12. SANGEETHA said

    its a great work…I found this article very useful..Thanks

  13. MANOHAR said

    its a great article for long period of time.it has complete information…..thanksalot.

  14. kumar said

    Thanks
    It helped a lot !!!

  15. kumar said

    Hi
    Is there any way to create SSIS Job for Data extraction from Oracle Cubes

    Regards
    Kumar

  16. Eswaran said

    Thanks for sharing such a great article

  17. [...] Number for a particular SQL Server InstanceDifferences between different SQL Server 2008 EditionsScheduling SSIS Packages with SQL Server AgentThe user is not associated with a trusted SQL Server ConnectionFunctional difference between "NOT [...]

  18. Gisele said

    Hi, is there any way to create SSIS jobs to extract data from MS ACCESS DataBase ? I tried an got :

    Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE
    DB provider Microsoft.Jet.OLEDB.4.0 is not registered — perhaps no 64-bit provider
    is available. Error code: 0x00000000. An OLE DB record is available. Source:
    “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not
    registered”.

    • Joshua said

      If you are running the package 64-bit you need to run it 32-bit. There are no 64-bit Access drivers (Hmm… does MS not think Access has future? Why didn’t they port the driver to 64-bit?).

  19. Mohon said

    Thanks a lot. It helped me.

  20. Naveed Nadaf said

    Its a great help. Thanks a lot

  21. vinoth said

    Dear Friend,
    I created SSIS package contain with ,
    1. Three Flat File connections and One Database connection.
    2. All connections are daily dynamically read data from flat, database connections and import into Sql database.
    3. I try to put on this package in Scheduler but it was failed through login error and some time only database connection data only input into the database.
    4. How can i solve this problem.

    Please help me out.
    thnks

  22. shankar said

    Thanks.
    Procedure helped me a lot. :-)

  23. Rafael said

    Thanks a lot. It helped me!!!

  24. [...] http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/ [...]

  25. Vishal Anand said

    Thanks,My problem go solved from the exlpanatoin.

  26. bishu said

    That was Awsome..!! thanks great Help..!!!

  27. Manoj Kumar Singh said

    I am trying to start my sql server agent but it is not getting started . Showing the following Error-Object reference not set to an instance of an object.

    I had made a scheduling a job.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: