Changing Connection string in multiple packages.

Changing Connection string in multiple packages.

• Capitán Cavernícola

  Yes they are :)

  Both the actual data servers and the SSIS server are clustered (and seperate). The plan is to have all packages run from one cluster so they're easier to find (compared to spread out over 40-50 servers as our dts packages are now) and easier to manage.

  We've discussed using a cluster file share, but again we would have to be able to make that work on so many different servers, every developer every development server... oi.

  In theory this is all great, but in practice we were hit with the changing of connections.

  
  
  

• Mike Vargas

  Nope I wouldn't have read it. :) That's an ops thing. But I'll pass it on to our DBA's. I have to code to the environment I,m given.

  One thing about the failover is it works even if the entire server is down, not just the Service. Say they want to put SP1 on the ssis box, this way the put failover to the second box, upgrade the first, it then falls back on the first box and they can update the second (including reboots) and we never lose a step.

  
  
  

• CE31572

  Thank you Mike that's much closer to what I'm looking for, I'll start playing with that and repost when I get a final answer. (or more questions which seems likely with SSIS :)

  
  
  

• The MATRIX

  Failover, if the SSIS server goes down, then the second one takes over and continues to run the jobs as scheduled.
  
  

• Bob Villa

  Ches,

  I have some script tasks in various packages that change connection manager databases on the fly from within a package. Here is the code:

  Dim oCon As ConnectionManager
  Dim oProp As DtsProperty
  oCon = Dts.Connections("ConnectionManagerX")
  oProp = oCon.Properties("InitialCatalog")
  oProp.SetValue(oCon, Dts.Variables("DatabaseNameVariable").Value.ToString)
  Dts.TaskResult = Dts.Results.Success

  Obviously, this runs from within the target package and is pulling the database name from a package variable, which isn't quite what you're looking for.

  I think you'd be going for something like this:

  Const c_sTargetPkg As String = "This is my package"
  Const c_sTargetServer As String = "Server1"
  Const c_sTargetConnection As String = "Connection A"
  Dim oApp As Application = New Application()
  Dim oPkg As Package
  Dim oCon As ConnectionManager
  Dim oProp As DtsProperty

  'Get administrative values from package variables
  Dim sConnectionName As String = Dts.Variables("TargetConnection").Value.ToString
  Dim sNewServerName As String = Dts.Variables("NewServer").Value.ToString
  Dim sNewDatabaseName As String = Dts.Variables("NewDatabase").Value.ToString

  'Get a handle on the target package and connection manager
  oPkg = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "", "", Nothing)
  oCon = oPkg.Connections(sConnectionName)

  'Change the server name
  oProp = oCon.Properties("ServerName")
  oProp.SetValue(oCon, sNewServerName)

  'Change the database name
  oProp = oCon.Properties("InitialCatalog")
  oProp.SetValue(oCon, sNewDatabaseName)

  'Save the package
  oApp.SaveToSqlServer(oPkg, Nothing, c_sTargetServer, Nothing, Nothing)

  It uses some constants and some package variables to load up a target package, adjust a particular connection manager's properties and then saves the package back to the target server. I'm sure you could add all kinds of looping or target package lists to this code to have it handle all packages within one run.

  Mike Ogilvie
  Pendulum, Inc.
  http://www.PendulumSolutions.com

  
  
  

• owl666

  Yes we've discussed using an XML Configuration, however... it causes it's own issues. Our production servers are clustered so anything having to point to a file would have to be set up on many many servers.

  And the same path would have to exist on all development servers, all developers workstations and all production servers. Maintenance nightmare.

  It may be the way we have to go, but... not really a path we want to go down.

  
  
  

• PeterVrenken

  Which all leads us back to my original problem.

  What I'm looking for is the code/syntax to change a connection in an existing package through another package to use as a deployment utility for chaning dev connections to production ones.

  Any pointers as to where to look for the syntax would be greatly appreciated.

  
  
  

• Roxana Ungureanu

  This might help: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

  -Jamie

  
  
  

• Justinas

  Ches Weldishofer wrote:

  We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

  Alternatively you could use SSIS's built-in mechanism for doing this. i.e. Configurations.

  You can share an XML configuration file between many packages. If your connection managers are identically named in each package then there won't be a problem.

  -Jamie

  
  
  

• Michael Wolff

  Interesting. Are you clustering your servers that run the ETL jobs Why can you not run all jobs on a single server That's not rhetoric, I'm interested to know.

  -Jamie

  
  
  

• Jack gillespie

  OK gotcha.

  I assume you've read this post on clustering: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

  You know that packages are not dependant on the SSIS service in order to run right

  -Jamie

  
  
  

• justinv

  I wrote my own vb app to maintain my connection strings in a data table in sql. I have all of my packages querying it and loading the connection strings to variables in memory and Iset them as an expression for the connection strings that I need.
  

• Bindia Hallauer

  Here's the code I finally used, it's inside a dataset enumerated loop to change which ever packages exist in the msdb root by name supplied in a variable (or % for all).

  Worked nicely, the dba is much happier with me now that he doesnt' have to change 130 connections.

  Dim c_sTargetPkg As String = Dts.Variables("DTSXName").Value.ToString
  Const c_sTargetServer As String = "servername"
  Const c_sTargetConnection As String = "ConnectionName"
  Dim oApp As Application = New Application()
  Dim oPKG As Package
  Dim oCon As ConnectionManager
  Dim oProp As DtsProperty
  

  'Get administratvive values from package variables
  Dim sConnectionName As String = Dts.Variables("ConnectionName").Value.ToString
  Dim sNewServerName As String = Dts.Variables("NewServerName").Value.ToString
  Dim sNewConnectionString As String = Dts.Variables("NewConString").Value.ToString

  'Get package and connection manager
  oPKG = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "login", "password", Nothing)
  oCon = oPKG.Connections(sConnectionName)

  'Change Server name
  oProp = oCon.Properties("ServerName")
  oProp.SetValue(oCon, sNewServerName)
  oProp = oCon.Properties("ConnectionString")
  oProp.SetValue(oCon, sNewConnectionString)

  'Save the packages
  oApp.SaveToSqlServer(oPKG, Nothing, c_sTargetServer, "login", "password")

  Dts.TaskResult = Dts.Results.Success

  
  
  

• Amol Gogate

  Right. Any reason why your ETL machine can't be non-clustered but still keep your data servers as clustered What's the advantage of clustering your ETL server

  Forgive me if I ask daft questions - I don't know much about clustering.

  -Jamie

  
  
  
• Changing Connection string in multiple packages.