September 4, 2010 at 4:58 am
Hi
I've created a SSIS package named SSIS1. The control flow has two tasks:
1. Execute Sql Task (which fetches data from few tables and inserts data into another table, tblOutPut).
2. Data Flow task (Source: OLE DB Source Destination : Flat File). The records from tbloutput should go into the flat file.
The package is executing fine from BIDS.
I referred to the following links for calling SSIS from SP:
http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx
But when I call the SSIS from SP using xp_cmdshell & dtexec /F "E:\BIDS\Package.dtsx", I get the following error:
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...."
Please note that I did NOT use any parameter or any config file.
Kindly let me know the easiest method to call from sp for the above SSIS package.
Regards
Mohan Kumar VS
September 4, 2010 at 2:14 pm
The problem is the default ProtectionLevel that packages get built with in BIDS - EncryptSensitiveWithUserKey. You can execute the package find in BIDS, because YOU are doing it, and the system can decrypt the passwords and other sensitive information in the package. But when you run it through SQL, it's not you that's executing the package, it's the account that SQL is running under. It can't decrypt those sensitive values. So, you need to change that ProtectionLevel to something else - typically people change it to EncryptSensitiveWithPassword, so they can put the password in the command line when they call the package.
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply