An easy way to rename oledb connection in a series of slowly changing dimensions

Problem

Over time we had developed many SSIS packages on a SQL 2008 server that transformed data over several databases. Each package used its own configuration file in which the same connections were defined again and again. To standardize the use of the database connections in all packages and to ease the use in development, testing and production environment, we decided to remove the connection definitions from the package configuration and put them in a separate configuration file. To achieve this, standardization of the connection manager names is required as well.

Instead of having one dtsConfig file for each package with:

• variables
• connection strings
  • the connection definition is repeated in different packages
  • the connection name is not standard

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales.ADO.NET].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value1].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>10</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value2].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>200</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

We opted for several dtsConfig files per package:

• dtsConfig with variables specific to the connection

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value1].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>10</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Value2].Properties[Value]" ValueType="Int32">

    <ConfiguredValue>200</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

• dtsConfig with connections specific to a database
  
  
  
  • sometimes an oledb and an ADO connection
    
  • the connection are renamed accordingly ( .._oledb, .._ADO)
    
  • the same dtsConfig file can be used by more than one SSIS package

<?xml version="1.0"?>

<DTSConfiguration>

  <DTSConfigurationHeading>

    <DTSConfigurationFileInfo GeneratedBy="Mydomain\jdhondt" GeneratedFromPackageName="SomePackage" GeneratedFromPackageID="{86BF1AAA-9397-4DC5-A45A-CC89D6B478F0}" GeneratedDate="25/10/2011 12:07:28" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[Sales.ADO.NET].Properties[ConnectionString]" ValueType="String">

    <ConfiguredValue>Data Source=DBSERVER;User ID=DBuser;Initial Catalog=Sales;Application Name=SSIS_Import;</ConfiguredValue>

  </Configuration>

</DTSConfiguration>

The big problem lies with the use of the package designer to rename a connection in existing Data Flow Sources or Slowly Changing Dimensions. Each time you dare to edit one of these and change the connection manager, the column names are lost and you have to redefine every one of them. The other problem is that you cannot easily find where the connection managers are being used.

Solution

• Make a backup of your package file.
  
• Use Visual Studio 2008
  
• With the package editor, add the new connection names, keep the old ones.
  
• Save the package
  
• close the designer
  
• In the solution explorer, right click on the package and select ‘view code’ in the dropdown menu.
  
• In the xml code, look for the ConnectionManager definition of the old connection and take note of the DTSID value
  
  
  
  

  
  

  <DTS:ConnectionManager>

  <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>

  <DTS:Property DTS:Name="ObjectName">Sales</DTS:Property>

  <DTS:Property DTS:Name="DTSID">{495F9DDC-1119-4A10-B67C-BEE6F3C35E72}</DTS:Property>

  
  
  

• now look for the ConnectionManager definition of the new connection and take note of the DTSID value
  
  
  
  

  <DTS:Property DTS:Name="DelayValidation">-1</DTS:Property>

  <DTS:Property DTS:Name="ObjectName">Sales_oledb</DTS:Property>

  <DTS:Property DTS:Name="DTSID">{06ACAA06-FE6F-4F94-B8D2-4164E9D33F9E}</DTS:Property>

  
  
  
  
• Find and replace the old DTSID everywhere in the code (except on the connectionManager definition), e.g. somehwere in the code where a slowly changing dimension is used:
  
  
  
  

  <connections>

  <connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{06ACAA06-FE6F-4F94-B8D2-4164E9D33F9E}"/>

  </connections>

  
  
  
  
• Save the package file and close the code editor window.
  
• Open the package with the package designer. Verify the connections in the Data flow Sources or slowly Changing Dimensions, The new name should be in there.
  
• Remove the old connection from the connection manager.
  
• Save and close the package.
  
• Edit the old dtsconfig file and delete the old connection definition.
  
• Open the package.
  
• Setup the package configuration to use the new database dtsconfig file.