Search code examples
ssisxml-configuration

XML SSIS Configuration File

I am trying to modify the connection string of multiple packages through XML configuration, but I am confused on where exactly to replace the server name (databases have the same name). the Old server is EREUUWSSQLD1\UWS and the one I want the package to load data in is EREUXUBSQLD1\XUBER_REPORTS I tried modifying some values but my package still loading data into the old server and when I change all of them it gives me an error.

Could you please help me figure this out.

Configuration ConfiguredType="Property"

Path="\Package.Connections[EREUUWSSQLD1\UWS.GalaxyReportingDW].Properties[ConnectionString]" ValueType="String">Data Source=EREUXUBSQLD1\XUBER_REPORTS;Initial Catalog=GalaxyReportingDW;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;EREUXUBSQLD1\XUBER_REPORTSData Source=EREUXUBSQLD1\XUBER_REPORTS;Initial Catalog=GalaxyReportingStaging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

ValueType="String">EREUXUBSQLD1\XUBER_REPORTSData Source=EREUXUBSQLD1\XUBER_REPORTS;Initial Catalog=ReportingDataMart;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;EREUXUBSQLD1\XUBER_REPORTS

Solution

  • First, you are trying to set the connection string for multitude of packages. XML Configuration File is an obsolete technique since SSIS 2012. If you have SSIS 2012+, I strongly recommend switching to its new technologies - Project Deployment Model, SSIS Catalog and Environment variables.
    Project Deployment allows you to share same set of Connection Managers among several packages in the Project, and modify Conn Manager properties on the Server for all packages at once. Here is a good intro into the subject.

    On your question. In order to utilize Package configuration you have to perform the following steps on all packages that use XML Config File:

    1. Open package, open Package Configurations Organizer.
    2. Define package configuration for the Connection Manager ConnectionString attribute. Specify the same filename for all files so they share the same configuration.
    3. Save Package Configuration. VS will generate a valid dtsConfig file

    Then, modify the config file, changing only <ConfiguredValue> node value. This node contains CM Connection String. For you task - you need to change DataSource=<your servername>; part.

    Sample of valid dtsConfig file from one of projects: 

    <?xml version="1.0"?>
<DTSConfiguration>
    <DTSConfigurationHeading>
        <DTSConfigurationFileInfo GeneratedBy="Server\Administrator" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{FF776D97-39A5-4302-AC4C-3DF61B8A0817}" GeneratedDate="22.11.2011 17:58:58"/>
    </DTSConfigurationHeading>
    <Configuration ConfiguredType="Property" Path="\Package.Connections[ConnManager_Name].Properties[ConnectionString]" ValueType="String">
        <ConfiguredValue>Data Source=ServerName;Initial Catalog=DB_Name;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
    </Configuration>
</DTSConfiguration>