Configuring a package for run time.

Sensitive data (passwords)
When a package is created the default is to save sensitive information (e.g. passwords) with a user key.
This is not very useful if the package is to be independent and run as any user.
A more flexible solution is to save the data externally and for the package to read this data when it executes.
Fortunately this is catered for with package configurations.
There are a number of storage methods to save the configuration information but I will deal with xml files here and show saving the Oracle server password.

Load the package into the designer
For this you will need a project so right click on SSIS Packages in any project.
Add existing Package and select the package.
Note that the package will be copied to the default location for the project so save it using save as… to overwrite an existing package.

Double click on the package in the solution explorer pane to bring it into the designer window.
At the top of the window click on SISS, Package Configurations…
This will bring up the Package Configurations Organiser.
Click on Add… to add a new configuration.
Select configuration type = XML Configuration File
Select Specify configuration settings directly.
Click on Browse… 
Select the folder and file name to create – e.g. MyConfig.dtsConfig.
Click next to select the properties.
Expand Connection Managers.
Expand the Oracle Connection.
Expand Properties
Select Password
Click next
Change the Configuration Name and click Finish.
Now click close to complete the creation of the configuration.

In the package properties change ProtectionLevel to DontSaveSensitive.
This means that the sensitive information will be received from the xml file rather than using keys to protect it.
Now save the package.

Open the xml file created with the editor of your choice (e.g. notepad).
There will be a DTSConfiguration entry with the connection and property ([password]) referenced.
The password will not be present but should be entered between the  tags e.g. mypassword.

Save the configuration file.

Now the package can be run by any user and will retrieve the password for the Oracle server from the xml file.


To run the package from the SQL Server scheduler.

Open SQL Server Management Studio
Expand the Server
Expand SQL Server Agent
Right Click on Jobs, New Job…
Create a step
Type = SQL Server Integration Services Package
Package Source = File System
Under package enter or search for the package file.
Click OK.

When this job is run it will retrieve the Oracle server password from the xml file and should connect happily.