Deploy SSIS via a Job
SQL 2008 R2 I have created an SSIS package that works perfectly when manually ran. I am now trying to run it as a Job. I have created a Job step with Type: SQL Server Integration Services Package Run as: SQL Server Agent Service Account For Package source I have tried a few options. When I use SQL Server I can not see the package to select it. When I use File system and put the path in I get the following error when I run it. Executed as user: SQL1\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:41:21 PM Error: 2012-05-14 15:41:21.03 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: 2012-05-14 15:41:21.14 Code: 0xC0202009 Source: CHRobinson Connection manager "SQL1.M2MDATA01.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2012-05-14 15:41:21.14 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "SQL1.M2MDATA01.sa". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:41:21 PM Finished: 3:41:21 PM Elapsed: 0.156 seconds. The package execution failed. The step failed.
May 14th, 2012 4:12pm

You need to change the package protection to Rely on Server for StorageArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 4:20pm

How did you set Protection Level? you can use SaveSensitiveWithPassword, or RelyOnServerStorage as Arthur said, this link shows how to set protection level of package when you want to deploy: http://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/http://www.rad.pasfu.com
May 14th, 2012 4:24pm

When I change it to that and tried to save I get an error: Failure saving package. Additional information: failed to apply package protection with error 0xC0014061 "the protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml. (CHRobinson)
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 4:28pm

The Server Storage protection level can only be used when saving to SQL Server, which was not know that you do not use that, right? Then your other option is the protect sensitive with password and provide it.Arthur My Blog
May 14th, 2012 4:31pm

I think I would prefer to save it to the SQL server but I don't know how to do that. I changed it now to protect sensitive with password but I don't know how to provide the password for the job.
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 4:47pm

It is a good idea, now you must log into the SSIS Instance in your SQL Server Management Studio, expand the tree until you get to the MSDB folder. Right-click and select Import. In the dialog box, select File System and then navigate to the folder where your package resides, choose the RelyOnServerStorage option and if prompted with password apply it. At this stage you must be all set and see the package in the MSDB.Arthur My Blog
May 14th, 2012 4:50pm

I am having a hard time following your steps. When you state MSDB are you refering to the database? If so, when I right click there is no import. If I right click and go to tasks I see "Import Data". If I choose that I don't see File System as an option.
Free Windows Admin Tool Kit Click here and download it now
May 14th, 2012 5:23pm

You must log into the SSIS Instance in your SQL Server Management Studio, expand the tree until you get to the MSDB folder MSDB is indeed the system SQL Server database, but you need to connect to the SSIS engine, not the database engine.Arthur My Blog
May 15th, 2012 10:41am

I now have my package stored on the server! I can run it manually from there without an issue. However I still can't get my job to run it successfully. I get the error: Message Executed as user: SQL1\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:56:39 AM Error: 2012-05-15 11:56:39.19 Code: 0xC0202009 Source: CHRobinson Connection manager "SQL1.M2MDATA01.sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2012-05-15 11:56:39.19 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "SQL1.M2MDATA01.sa". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:56:39 AM Finished: 11:56:39 AM Elapsed: 0.188 seconds. The package execution failed. The step failed. I have tried both SQL and Windows Authentications (on the General tab) but both ways seem to give me the same error.
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 12:06pm

You have a connectivity issue running the package from the Agent I guess, because the Agent 's account has no rights over the db. Use a proxy instead then as described here: http://www.databasejournal.com/features/mssql/article.php/3789881/Proxy-Accounts-in-SQL-Server.htmArthur My Blog
May 15th, 2012 12:10pm

I glanced at the article but it seems very complicated. Instead of creating the proxy I granted SQLSERVERAGENT login all rights under "Server Roles". I figured this would ensure it has any permissions that it needs. I know this is not secure but I am not really concerned about that. After changing the security I ran the job again but got the same error.
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 12:23pm

Your issue based on the error " Failed to decrypt protected XML node "DTS:Password" is in package protection level. Once you change it to relyOnServerStorage it must work. Remove it, then re-add specifying this protection mode. Right now the package is left in the default protection mode set by BIDS to EncryptWithUserkey which will never work w/o your account.Arthur My Blog
May 15th, 2012 1:39pm

When I change the protectionLevel to ServerStorage and try to save I get an error. Failure saving package. Additional information: failed to apply package protection with error 0xC0014061 "the protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml. (CHRobinson)
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2012 2:12pm

It is because you did NOT place it into the MSDB, since it is residing in the file system this level is not applicable. To correct, change the package protection level to SaveSensitiveWithPassword. Give it a good password, but then when you need to run the package using the Agent you need to modify the command line to supply it, otherwise you will keep getting the original error (because the package is still encrypted).Arthur My Blog
May 15th, 2012 2:17pm

Thank you for your patience with me. I have now gotten the job to run!
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2012 9:48am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics