How to Update connection string in multiple SSIS packages via t-sql

  • I have some 100+ SSIS packages that have connection strings embedded with-in the packages. We are moving to a new server. I can redesign the packages to pull the connection string dynamically from a table/xml/config file. I am keeping it as the last option due to lack of resources.

    As a quick solution, I was hoping I could update the SSIS package code via T-sql and not have to open and update the connection string on all 110 SSIS packages manually. If you have other suggestions like scripting all SSIS packages, and then doing a global find and replace of the connection string, I am open to that.

    So what I am looking for here is a quick fix. The correct long term fix would be to pull the values out to a config file.

    Thanks for your time

  • Keeping in mind that SSIS packages when stored in SQL are essentially BLOBs a find and replace is tricky. Also keeping in mind that depending on the contents of the package the connection string may be encrypted only makes it more difficult if not impossible.

    While not very palatable I'm thinking the retro-fitting of package configs is the best method, and not to put too fine a point on it, would have prevented this from being such as issue if done the first time..

    I would suggest that method above the opening and changing the connection strings in each package directly method since it effectively leaves you in the same boat, AGAIN. This reminds me of a phrase "we ain't got time to do it right, but we got time to do it twice."

    CEWII

  • Agree with that point. I will have to bite the bullet and do it the "right" way. Thanks

  • I feel your pain.. Hope it goes well for you.

    CEWII

  • deepmustang 90114 (12/13/2011)


    I have some 100+ SSIS packages that have connection strings embedded with-in the packages. We are moving to a new server. I can redesign the packages to pull the connection string dynamically from a table/xml/config file. I am keeping it as the last option due to lack of resources.

    As a quick solution, I was hoping I could update the SSIS package code via T-sql and not have to open and update the connection string on all 110 SSIS packages manually. If you have other suggestions like scripting all SSIS packages, and then doing a global find and replace of the connection string, I am open to that.

    So what I am looking for here is a quick fix. The correct long term fix would be to pull the values out to a config file.

    Thanks for your time

    Although package configurations are surely the best option, a quick fix is that you get yourself a decent flat file editor and do a search-and-replace in all files at the same time (they're just a bunch of xml files).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This is an ideal scenario for creating package configurations. when creating a configuration elect to use SQL Server roles and security. Export your chosen connection string objects and they are stored in a config table. You may then dynamically update these for a single package, set of packages or all packages.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Koen Verbeeck (12/13/2011)


    Although package configurations are surely the best option, a quick fix is that you get yourself a decent flat file editor and do a search-and-replace in all files at the same time (they're just a bunch of xml files).

    In an ideal world that may be ok, if they are using trusted security.. In that case the data will be encrypted and editing the text will not be an option.. But I wholly agree they are just big XML files.

    CEWII

  • Elliott Whitlow (12/14/2011)


    Koen Verbeeck (12/13/2011)


    Although package configurations are surely the best option, a quick fix is that you get yourself a decent flat file editor and do a search-and-replace in all files at the same time (they're just a bunch of xml files).

    In an ideal world that may be ok, if they are using trusted security.. In that case the data will be encrypted and editing the text will not be an option.. But I wholly agree they are just big XML files.

    CEWII

    Ah, good point. I'm used to work with DontSaveSensitive and Windows Authentication, so lucky me 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (12/14/2011)


    Ah, good point. I'm used to work with DontSaveSensitive and Windows Authentication, so lucky me 🙂

    That is what I push for too, and it has saved me a lot of heartache..

    CEWII

  • Realize ths is an old post. But it showed up first when I was reseraching my issue. So thought that I would post my answer.

    OK. got this to work for SQL 2008 forward. This will change text values in an image data type. It will only change the first occurence of the search text. Also currently coded to change a single package (Where name = 'CreateSSISPackage')

    DECLARE @SearchText varchar(8000)

    DECLARE @SearchTextOffset Int

    DECLARE @SearchTextLen Int

    DECLARE @PreviewTextSize Int

    DECLARE @NewText varchar(8000)

    SET @SearchText = 'THEBRENDA' -- ***************** Change this

    SET @NewText = 'THEBRENDAx' -- ***************** change this

    SET @PreviewTextSize = 50

    Select name as [Package Name],

    folderid as [FolderID],

    Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@SEARCHTEXT, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -

    @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SEARCHTEXT, '***' + @SEARCHTEXT + '***') As [Search Text Preview],

    packagedata as [Package Data]

    from MSDB.dbo.sysssisPackages

    Where name = 'CreateSSISPackage'

    set @SearchTextLen = LEN(@SearchText)

    set @SearchTextOffset =

    (select

    CharIndex(@SearchText, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)))

    From MSDB.dbo.sysssisPackages

    Where name = 'CreateSSISPackage'

    )

    set @SearchTextOffset = @SearchTextOffset - 1

    print cast(@SearchTextOffset as varchar(10))

    DECLARE @ptrval binary(16);

    SELECT @ptrval = TEXTPTR(Packagedata)

    FROM MSDB.dbo.sysssisPackages

    WHERE name = 'CreateSSISPackage'

    UPDATETEXT MSDB.dbo.sysssisPackages.Packagedata @ptrval @SearchTextOffset @SearchTextLen @NewText;

    Select name as [Package Name],

    folderid as [FolderID],

    Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)), CharIndex(@NewText, Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -

    @PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @NewText, '***' + @NewText + '***') As [Updated Search Text Preview],

    packagedata as [Package Data]

    from MSDB.dbo.sysssisPackages

    Where name = 'CreateSSISPackage'

  • Good one. Very useful.

    Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply