June 19, 2009 at 11:31 am
I need to add an entry to my SSIS.dtsConfig file to accomodate access to an Oracle database.
Don't want to get into the WHY's here, but we had to install the Oracle 10g Provider for OLE DB. (rather than use Microsoft's Oracle provider)
Does anyone have sample XML to use for this type of connection with my dtsConfig file?
June 19, 2009 at 1:40 pm
This link was somewhat helpful:
http://www.nigelrivett.net/DTS/SSISConfigurationFile.html
Essentially, you manually setupo your connecion to your Oracle Server & DB.. then generte the XML file using SSIS Designer tools:
- 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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply