What to do when an SSIS Package Fails as a Job on Startup.

Ah, the good ole' days of yesteryear and SQL Server 2000 DTS Package Scheduling are over. Remember when DTS was the DBA's best friend, and you can schedule a package to run as job without issue?

Scenario I – The 5PM deadline. You're called into an ad-hoc meeting, and suddenly you're sitting on one side of the table with all eyes on you, the DBA, receiving the latest instructions. "We have a report due by 5PM, and we need to get data quickly from Server A to Server B, so we can run reports and not touch production. We also need to have this scheduled and run daily" While all the room is awash in panic and pale white faces decorate the conference room; you sit there confidently and say "No Problem, I'll have it by 4PM." As you begin to make your way back to your desk to begin building the package, you manager tells you this is for the SQL Server 2005 instance. "OK, this should be easy", you think to yourself, "2005 has wizards too. I'll click a few buttons, and make it a scheduled job and it will run by 4PM, and give me a chance to catch up on the latest articles and blogs on SQLServerCentral.com"

After I open up my package in BIDS to do a dry run, all icons are green and we're good to go.

Then, as you run a test job, refreshing the SQL Job Activity Monitor expecting to see a "Success" message. You rub your eyes; it says Job "Failed". And, you right click to further investigate, and you see a cryptic message:

Description: Failed to decrypt protected XML node "DTSassword" 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"

Say what?!! And what exactly is a 'DTS assword'? This works fine when in the Business Intelligence Design Studio (BIDS), but when I try to set it up to run from the SQL Server Agent I get nothing but problems.

Generally, this happens because the SSIS package was encrypted with a user key that different from the key of the SQL Server Agent Impersonated Service Account.

So, by now you know this is no longer your 2000 DTS scheduled job. Remember when 2005 first came out; Microsoft introduced it as 'Secure by Default' Welcome to a more secure world! In order to get our package to run as a scheduled job, we must consider the security layer, and setup a proxy account, create the proper credentials, and ensure that job agent has the right roles, and that the PROXY account has required permissions as well.

To learn more about the new security features and improvements in the 2005 SQL Server Agent job scheduling service, see this Microsoft Technet article: 'What's New in SQL Server Agent for Microsoft SQL Server 2005'. It describes the new roles in the msdb database SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole. In addition, it describes what the proxy accounts are that are needed for each type of job step, including an SSIS Package.

I would recommend reading the TechNet article first to get an understanding on how the new security layers work and get familiar with these roles. Basically, you need to create your 'Credential', based on your Identity, setup the proxy account based on your credentials. I will demonstrate the steps here:

In your SQL Server Management Studio, drill down to the Security folder for your database server, select Credentials, right-click and select 'New Credential'. This will bring up this window:

Enter a name for your credential, and give your identity to the credential, which is usually a domain\user account, which will serve as the proxy account. Once you click <OK> to create the credential, you must now create the Proxy.

Continue to drill-down in SSMS, to the SQLAgent -> then the 'Proxies' folder.

Here you will be presented with several subsystems that can run under the agent, but here we are looking to run an SSIS package, and therefore will select 'SSIS Package Execution', and right-click

On this screen, enter a name for your Proxy, and in our example, select SQL Server Integration Services Package. Click the ellipsis button to browse for your Credential name that we created above, select it and click <OK> three times to apply and close out the screens. You have now created your proxy with the proper credentials.

If you refer to the aforementioned TechNet, you will get the t-sql equivalent to create the requirements.

Microsoft has a KB article, http://support.microsoft.com/kb/918760 that tries to help with an SSIS package does not run when you call the SSIS package from a SQL Server Agent job step, and describes the various possible methods to set this up and make it work.

The things here to keep in mind is that there are several interconnected components working together that are outside the SSIS package that require the user account to have the proper permissions to connect to these outside resources (ie: the job agent, excel, file system, etc.) Also, when you move the package around or deploy it to another server, the user account that is used to run the package under SQL Server Agent differs from the original package author.

Finally, take a look at this good summary document posted on codeproject.com on 'How to Schedule and Run a SSIS Package job'. The author here breaks it down in to three main steps:

• The job executor account needs the roles of sysadmin, SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole ;
• The job needs to be run under Proxy account;
• The job executor account is better to be used to create SSIS package and so you are sure the job executor account has the right to run this SSIS package.

In conclusion, I hope that after you read the above article, you will understand the intricacies of the security model needed to successfully run your SSIS package as a scheduled job. And, hope that now you will make that 5PM deadline!

Pearl Knowledge Solutions, Inc., is pleased to announce that it is now offering qualityDBA managed services and remote 24x7x365 monitoring from its network of skilled DBA experts! To inquire about our services, and obtain a FREE SQL Server report, contact us at sqlcentric@pearlknows.com.

Quality DBA Services from the folks who brought you:

When SQL is the Center of Your Universe!

Coming Soon! We are getting ready to release our SQLCentric SQLOS live Performance Dashboard! Our dashboard will be available as a stand-alone solution, or fully integrated with SQLCentric. See all critical performance measures and vital stats from one web console in real-time! Feel free to contact us for more info.