I recently created a SSIS package which copied data from an Oracle DB to a SQL Server Database. The pacage I created using BIDS worked fine when executed by me but wouldn’t run via agent.
A quick search located this knowledge base article. http://support.microsoft.com/kb/918760.
This entry focuses method 4, my prefered method.
Use SSIS Package Configuration Files.
The default security setting when a package is created is to save passwords and other sensitive information within the package with a user key. This means only the account that created the package can access and run it. This is not very helpful when you need the SQL Server Agent Account to run the package. To solve this we will create an XML file which stores the password and can be accessed by the package by anyone. This raises fresh security concerns which I solved by placing the configuration file on the server.
To create the XML File:
1) load the package into the designer.
2) Double click on the package in the solution explorer pane.
3) Click on Add…
4) Select XML Configuration File as Configuration Type.
5) Select specify Configuration Settings directly.
6) Click on Browse.
7) Select where you want to save your file and name it eg Config.dtsConfig.
8 ) Click next.
9) Expand Connection Managers -> Oracle Connection -> Properties.
10) Select Password.
11) Click next.
12) Change the configuration name.
13) Click Finish and close.
Now navigate to package properties and change the ProtectionLevel to DontSaveSensitive. This tells the package that the password will be recieved from the XML file and won’t be saved with the package.
Navigate to the xml file and open it up with the XML editor of your choice (I prefer notepad).
To run the package from SQL Server Agent:
1) Open SQL Server Management Studio.
2) Expand the Server.
3) Expand SQL Server Agent.
4) Right click on jobs and click on New Job.
5) Create a Step.
6) Call the step something meaningful.
7) Select File System as Package Source.
8 ) Under the Package tab search for the package file.
9) Click OK
When the job is executed it will retrieve the Oracle Server Password from the XML File, connect and Execute.
Tags: SQL Server Agent, SSIS