none
Changing Connection string in multiple packages.

    Question

  • Scenario:

      130 dtsx packages using 4 matching connections. 
      3 of those connections are stored in an SSIS Configuration table in an Operational database.
      The last connection is in a shared data source and points to the Operational database so the packages can grab the others.

    Problem:

      It's time for deployment and all of those connections must change to production servers.
      The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
      However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages.  We all know what assumptions do to you.... So.  I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)

    Solution:

      It has been suggested that we use another package to run though all of the packages and change the connection with a script task.  I can live with this (and more importantly so can our DBS's who have to deploy). 

      I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.

    Has anyone else done this?  Or had a similar problem and way to fix?

    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.

    Ches Weldishofer
    ETL Developer
    Clear Channel Communications

     

    Wednesday, March 29, 2006 3:19 PM

Answers

  • 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

    Friday, March 31, 2006 5:40 PM

All replies

  •  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

     

    Wednesday, March 29, 2006 3:35 PM
    Moderator
  • 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.

    Wednesday, March 29, 2006 3:40 PM
  • 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

     

    Wednesday, March 29, 2006 3:57 PM
    Moderator
  • 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. 

    Wednesday, March 29, 2006 4:02 PM
  • 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

     

    Wednesday, March 29, 2006 4:08 PM
    Moderator
  • Failover, if the SSIS server goes down, then the second one takes over and continues to run the jobs as scheduled.
    Wednesday, March 29, 2006 4:12 PM
  • 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

     

    Wednesday, March 29, 2006 4:14 PM
    Moderator
  • 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.

    Wednesday, March 29, 2006 5:16 PM
  • 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.

    Wednesday, March 29, 2006 5:39 PM
  • Wednesday, March 29, 2006 6:22 PM
    Moderator
  • 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

     

     

    Wednesday, March 29, 2006 9:22 PM
  • 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 :)

    Wednesday, March 29, 2006 10:47 PM
  • 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.
    Wednesday, March 29, 2006 11:07 PM
  • 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

    Friday, March 31, 2006 5:40 PM
  • Hi,

    Thanks a lot for this very helpful post. It is amazing.

    But why when I open up the package and the ServerName for the connection is still the old ServerName? But when I return the connection string within the script, it does return the new connection string with the old servername being replaced by the  new server name?

    Is it the same result for you or did I miss something out here?

    Thanks a lot and I am looking forward to hearing from you for your further advice.

    Best regards,

    Yours sincerely,

     

    Wednesday, August 20, 2008 4:15 PM
  • PiggyZhou,

     

    The changes are being made through code, so it's only affecting the in-memory version of the package while it runs. Much in the same way that if you change a package variable during the package's execution, that new value does not persist to the saved package.

     

    If I'm understanding your question correctly.

     

    Mike Ogilvie

     

    Wednesday, August 20, 2008 4:31 PM
  • Hi,

    Thanks for the very helpful post.

    But why when I open up the package, the server name for the connection still remains the old server name while within the script it returns the new server name for the connection ? What is going wrong? Or that's the way it works?

    Can you please give me more advice on this? Thanks alot and I am looking forward to hearing from you.

    Best regards,

    Yours sincerely,

     

    Wednesday, August 20, 2008 4:53 PM
  • When you open the package from a file (.dtsx), the server names should be as they were the last time you saved the package to a file. When you open the package from a server, the server names should e as they were the last time you uploaded the package to that server.

     

    The script that makes changes to the server names, database names, or whatever within a connection manager (or that changes the values of package variables), is only making changes whent he package is executing and only to the COPY of the package that is executing in memory. Essentially, when you execute a package there exists 3 simultaneous copies of that package:

    1) The original package repository saved to a file or to an I.S. Server. (Unless you just started creating the package and haven't saved it yet.)

    2) The "development" version of the package that you loaded into the Visual Studio development environment. This is the package that you look at and make changes to. As soon as you hit Save, the changes you made here get pushed down to 1 (above).

    3) The "executing" version of the package that is running through Integration Services. This version of the package is in memory on your computer (wherver Visual Studio is running) but is a distinctly separate set of code from 2. Therefore, all changes that it does to itself are lost.

     

    Hopefully that clears things up a little. The only way to make a "permanent" change to the package is to save the package back to a file or to the server. I believe the point of the original question/code in this thread was to change connection managers on the fly, during execution. That's really a different thing from making changes to a package that will persist the next time you open it up.

     

    Mike Ogilvie

     

    Wednesday, August 20, 2008 6:11 PM
  • Hi, thanks a lot for your help.

    I am sorry I did not make it clear that the packages I am changing here are actually DTS packages.

    I just realized the package I loaded before was the SSIS package. And there is another DTS package with the same name on the SQL Server 2005 instance. Therefore when I use the LoadFromSqlServer function it only looked and loaded the SSIS version of it and changed it. And I was looking at the DTS version, so that's why I did nto see the change cos it actually did not do anything with the DTS one.

    I am now having problem with loading the DTS package. It seems all the loading packages functions with Script task (VB.NET) only works with SSIS packages instead of DTS packages? I cant load DTS package with either LoadPackage (to load DTS package from file system , e.g  on my C drive) or LoadFromSqlServer (Load from SQL Server Instance, either sql server 2000 or SQL server 2005 instance).       

    How can I load the DTS packages with Script task if it can do so?          

    Hope it is clear for your help and I am looking forward to hearing from you for your kind help and advices.

    With best regards,

    Yours sincerely,

     

    Wednesday, August 20, 2008 6:53 PM
  • That I probably won't be able to help you out with. I haven't really done any I.S. -> DTS cross-platform coding.

     

    I'm sure someone else on the forum has though. You may want to start a new thread.

     

    Wednesday, August 20, 2008 9:36 PM
  • Hi,

    Thanks a lot still for your help.

    Best regards,

     

     

     

    Wednesday, August 20, 2008 10:43 PM
  •  

    To answer my own question, I have written up a VB.NET application as follows and it works.

     

    Imports DTS
    Imports System
    Imports System.Data.SqlClient

     

    Module DTSUpdate
        Dim Opkg As DTS.Package = New DTS.Package()
        Dim PkgName As String
        Dim ServerName As String
        Dim ConnectionString As String
        Dim Con As Connections
        Dim NumOfCon As Integer
        Dim Str As String = ""
        Dim myConnection As SqlConnection
        Dim myCommand1 As SqlCommand
        Dim myCommand2 As SqlCommand

        Dim dr As SqlDataReader

     

     


        Sub Main()

            'Establish ODBC connections with SQL Server Instance
            myConnection = New SqlConnection("Server = MISQLD01; uid = username; pwd = password; database =msdb")

            Try
                'Opening the connection
                myConnection.Open()

                'Executing the command and assigning it to my connection
                myCommand1 = New SqlCommand("SELECT name FROM sysdtspackages", myConnection)
                'myCommand2 = New SqlCommand("SELECT COUNT(*) FROM sysdtspackages", myConnection)

                'Read from the datareader
                dr = myCommand1.ExecuteReader()
                While dr.Read()
                    'MsgBox(dr(0).ToString())

                    If (dr(0).ToString()).Equals("PACKAGENAME") Then
                        MsgBox(dr(0).ToString())


                        'Load DTS package from SQL Server
                        Opkg.LoadFromSQLServer("MISQLD01", "username", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())

                        ' Get the loaded DTS package name
                        PkgName = Opkg.Name()

                        'Get the number of connections within the loaded DTS package


                        'Get the connection string of each connections of the DTS package
                        For NumOfCon = 1 To Opkg.Connections.Count
                            If (Opkg.Connections.Item(NumOfCon).DataSource = "OLDSERVERNAME") Then
                                Opkg.Connections.Item(NumOfCon).DataSource = "NEWSERVERNAME"
                            End If
                            'MsgBox(Opkg.Connections.Item(NumOfCon).DataSource)
                        Next
                        'MsgBox(Opkg.Connections.Item(NumOfCon).DataSource)
                        'Opkg.Connections.Item(1).DataSource = "NEWSERVERNAME"
                        'MsgBox(Opkg.Connections.Item(1).DataSource)
                        Opkg.SaveToSQLServer("MISQLP01", "username", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", dr(0).ToString())

                    End If
                End While
                dr.Close()
                myConnection.Close()

            Catch ex As Exception

            End Try

        End Sub

     

     

    End Module

     

     

    Hope it helps if you are in the same situation seeking similar solution.

    Regards,


     

     

    Thursday, August 21, 2008 3:31 PM