dinsdag 6 december 2011

Bulk change SSIS Package configuration

Did you ever had a lot of SSIS packages that you needed to change a package configuration string in? Did you start with opening each package in BIDS and ended up with a clicking finger?

This is my situation, in my packages I do use an environment variable that points to a configuration database for the packages. The configuration database is used to store package configurations, package logging and audit information for process runs.

Let's say that the environment variable used is named ENV_SSISConfig.
It points to a connection string that uses initial catalog dbSSISConfig.

All packages have a package configuration using the environment variable ENV_SSISConfig, when you look at the code of the package you will see the line:
<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig</DTS:Property>
It's desided to give each SSIS project there own configuration database, for the current project this means the database dbSSISConfig is renamed to dbSSISConfig_DWH. No problem so far I can just change the ENV_SSISConfig environment variable to use the initial catalog dbSSISConfig_DWH.

But what when a second project is started? The environment variable ENV_SSISConfig is already in use.
So the first we did was renaming the ENV_SSISConfig to ENV_SSISConfig_DWH and then we need to edit each package created to change the environment variable ENV_SSISConfig to ENV_SSISConfig_DWH.

<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig</DTS:Property>
will become
<DTS:Property DTS:Name="ConfigurationString">ENV_SSISConfig_DWH</DTS:Property>

Current this means that we need to open 300 packages in BIDS to change the package configuration, with a few people this can be done quickly but I'm a bit lazy and did use the below VBScript to change the configuration in all packages.

' Create a FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")

Const ForReading = 1
Const ForWriting = 2

' Define folder containg the files whe like to replace the string in
strPath = "D:\SSIS\SSIS_DWH\Solution\MAIN"

Set folder = objFSO.GetFolder(strPath)
Set files = folder.Files

' Loop through each file  
For each item In files
 If UCase(objFSO.GetExtensionName(item.name)) = "DTSX" Then
      End If  

'Function to replace the string
Function myReplace(strFileName)
 Set objFile = objFSO.OpenTextFile(strFileName, ForReading)

'Define strings to replace Used chr(34) because string has double quotes that need to be searched
 strOldText = "<DTS:Property DTS:Name=" & Chr(34) & "ConfigurationString" & Chr(34) & ">ENV_SSISConfig</DTS:Property>"
 strNewText = "<DTS:Property DTS:Name=" & Chr(34) & "ConfigurationString" & Chr(34) & ">ENV_SSISConfig_DWH</DTS:Property>"

 strText = objFile.ReadAll
 strNewText = Replace(strText, strOldText, strNewText)

 Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
 objFile.WriteLine strNewText
End Function

Run the script from a command prompt: cscript quick_replace.vbs.
Ofcource before doing so I copied the solution\MAIN folder to solution\20111206 folder to make sure I could roleback when something should go wrong.

You could improve the script by execpting input parameters as folder, oldstring and newstring if you like. I tried that but had some issues with the double quotes containd in the replacement string. So quick & dirty worked for me.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter