THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

The Power of sed in PowerShell

SSIS is a great tool for managing ETL, but in a complex process it's easy to have a lot of packages all referencing the same data source. In testing I found I needed to change all the connection managers in all the packages to a different database. When you have dozens of packages that can be rather tedious.

Note: there may (and probably are) better ways to set up connections in SSIS.

In any event, SSIS stores its package files as DTSX files, but you can open them in a text editor and it's all XML, so I thought "hey, if I could use a command like the Unix "sed" command I could change all the connections at once!" So I did a quick search and found this great post: Replacing Strings in Multiple Files. They do a great job of going through the process step-by-step so I won't do the same. Their explanation is excellent.

Here's the PowerShell command I used to change my target database:

gci -ex AdventWks*.* | ? {$_.Attributes -ne "Directory"} | % { cp $_ "$($_).bak";
 (gc $_) -replace "AdventureWorksDW","AdventureWorksDW2008" | sc -path $_ }

Now, just in case anything went wrong it changes the existing file extension to '.bak', but then replaces the database name in all the packages in the solution. I opened up the solution and tested a couple of the connections, and it all worked just great!


Published Friday, July 9, 2010 11:41 AM by AllenMWhite



SQLvan said:

That's pretty neat Allen...

I found that the BIDShelper download allows you to change connection to multiple packages at once with the deploy command. All of this can be done inside SSIS.

July 9, 2010 2:45 PM
New Comments to this post are disabled

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Privacy Statement