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[192.168.1.1].Properties[ConnectionString]" ValueType="String"> <ConfiguredValue>Data Source=192.168.1.1;Initial Catalog=master;User ID=abc;Password=abc</ConfiguredValue> </Configuration> </DTSConfiguration>