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
myReplace(item.path)
End If
Next
'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
objFile.Close
strNewText = Replace(strText, strOldText, strNewText)
Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine strNewText
objFile.Close
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
.
