Hello All,
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
If you search this forum for "connection password" you'll get plenty of results. Basically, SSIS does not save passwords in connection strings.Verify that your package property, ProtectionLevel, is set to EncryptSensitiveWithUserKey. If that doesn't work, then you can put the connection string for the Oracle database in a configuration file, of which you can manually update to contain the password. All of this is posted throughout this forum.|||
Hi,
Thanks for your reply. The thing about that is, that the package is owned by the OS user I use, but the Server Agent runs on another profile (System), so encrypting with a user key will make the Agent unable to execute de package. I wil check out the configuratio file thing, though.
|||Then you can do EncryptSensitiveWithPassword and specify the package password in SQL Agent by altering the dtexec command line parameters.|||I ran into your problem and my solution was to change the CreatorName property of the package to the account that SQL Agent runs under, and I also changed the CreatorComputerName to the name of the production server, then it finally workedsql
No comments:
Post a Comment