Search and replace inside SSIS package

Search and replace inside SSIS package

I had the situation recently where I needed to search and replace a string inside an SSIS package. The reason for this was about 25 Execute SQL tasks within the package all included some hard coded T-SQL that pointed at a decommissioned network share.  I needed to change the T-SQL in each task to point to a new network share. I know the package should have used a variable for this but I needed a quick fix.

If you realise that your SSIS package is simply just some XML which is made to look graphical by SSDT or BIDS, it becomes clear that this is a very simple process. It is just like doing a replace all in notepad which most people will have done many times. This is much easier and quicker than manually opening each and every Execute SQL Task and making each change manually.

Steps

1. Make a copy of your SSIS package (just in case)
2. Right click on the .dtsx file and choose Open with -> Notepad
3. In notepad, press Ctrl-H
   1. Put the old value in Find What
   2. Put the new value in Replace with
   3. Click Replace All
4. Save your package
5. Open in SSDT or BIDS to confirm your changes have worked.