Dynamic Connection Strings in SSIS

Possible or not? -->
I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations

In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?

Function SetDTSenvironmentVariables( environment )
Folder = "MyDtsPackageFolder"
Select Case environment
case "DEV"
DTSGlobalVariables("WorkingDirectory").value = "C:\Packages" & Folder
case "STAGING"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\STAGING" & Folder
case "LIVE"
DTSGlobalVariables("WorkingDirectory").value = "D:\Sql_working_directory\My_production\" & Folder
End Select

'
' Set Connection Properties
'
dim oPackage, oConn
set oPackage = DTSGlobalVariables.parent
oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "\Logs\Errors.txt"
For Each oConn In oPackage.connections
Select Case oConn.Name
case "My_DB"
Select Case environment
case "DEV"
oConn.datasource = "SERVER01"
oConn.Catalog = "My_Production"
case "STAGING"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Staging"
case "LIVE"
oConn.datasource = "SERVER06"
oConn.Catalog = "My_Production"
End Select
case "Schools.xls"
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & "School_Codes.xls"
case else
oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "\" & oConn.Name
End Select
Next

set oPackage = nothing
set oConn = nothing
End Function

The way I do this is to have a variable called RootFolder and all other directories are relative to that and hence can be set dynamically using an expression (on ConnectionString property of the appropriate connection manager).

RootFolder variable is set via a configuration. Its your choice as to what type of configuration you use.

I kinda talk about this a bit here:

Common folder structure
(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2559.aspx)

-Jamie

|||Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...|||

TheViewMaster wrote:

Awesome, dude

Thanks mate - only thing I wonder how do you get time to write all those blogs...

I wonder myself sometime.

I've been doing it for two years tho so there's quite a library of "stuff" up there now. I hardly ever write anything new these days.

-Jamie