Tuesday, July 23, 2013

SSIS - connection string from XML

SSIS - getting connection string from XML file.
Step 1
Go to SSIS and select Package Configurations.
 Step 2
Add new configuration.
Step 3
Select Configuration type as XML Configuration file. Then select Specify configuration setting directly radio button and select the configuration file.
Step 4
Select the property which you want to configure. Here it is Connection string. Then click next.
Step 5
Give a name to this configuration.  And click on finish.
Hey you are done with this in just 5 click.
Here is the configuration file (abc.dtsConfig)

<?xml version="1.0"?> <DTSConfiguration> <DTSConfigurationHeading> <DTSConfigurationFileInfo GeneratedBy="HCLTECH\hriday.dutta" GeneratedFromPackageName="Package" GeneratedFromPackageID="{9F34C6C8-7D33-4E60-AA5A-1A95DA62D08B}" GeneratedDate="7/17/2013 2:48:57 PM"/> </DTSConfigurationHeading> <Configuration ConfiguredType="Property" Path="\Package.Connections[].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=;Initial Catalog=master;User ID=abc;Password=abc</ConfiguredValue> </Configuration> </DTSConfiguration>