SSIS Package configuration

[robert030975]

HI

I need to transfer information for one server to another and although it works in BIDs when I run SSIS package via the agent I get

rights reserved. Started: 10:58:45 AM Error: 2010-07-07 10:58:45.20 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". 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: 2010-07-07 10:58:49.78 Code: 0xC0202009 Source: Marketing Load Connection manager "EGH-SRV-00001\SQLEXPRESS.Shared.Marketing" 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 Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'Marketing'.". End Error Error: 2010-07-07 10:58:49.78 Code: 0xC020801C ... The package execution fa... The step failed.



The package is simple a couple of tables that transfer across with a package configuration using connection string and password for the Marketing server.



<Configuration ConfiguredType="Property" Path="\Package.Connections[EGH-SRV-00001\SQLEXPRESS.Shared.Marketing].Properties[ConnectionString]" ValueType="String"><ConfiguredValue>Data Source=EGH-SRV-00001\SQLEXPRESS;User ID=Marketing;Initial Catalog=Shared;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;</ConfiguredValue></Configuration><Configuration ConfiguredType="Property" Path="\Package.Connections[EGH-SRV-00001\SQLEXPRESS.Shared.Marketing].Properties[Password]" ValueType="Marketing01"><ConfiguredValue></ConfiguredValue></Configuration>


Any help would be appreicated thanks

 

[SQLBill]

You are probably saving the package with "encrypt sensitive data". The encryption uses a hash based on your login and the computer name (or something like that). So when you move it to another computer, the hash no longer matches.

I suggest saving the package with the option "Rely on server for protection" (that's not the exact wording, but close).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875