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 09, 2010 11:41 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



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

Leave a Comment


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


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement