December 13, 2011 at 10:33 am
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
December 13, 2011 at 11:10 am
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
December 13, 2011 at 4:58 pm
Agree with that point. I will have to bite the bullet and do it the "right" way. Thanks
December 13, 2011 at 7:34 pm
I feel your pain.. Hope it goes well for you.
CEWII
December 13, 2011 at 11:34 pm
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
December 14, 2011 at 1:40 am
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" 😉
December 14, 2011 at 8:28 am
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
December 14, 2011 at 11:39 pm
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
December 15, 2011 at 8:44 am
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
June 23, 2014 at 11:05 am
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'
April 1, 2016 at 2:00 pm
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