Find and Replace String for SSIS for mass amount of packages

Published on
3,131 Points
Last Modified:
Leo Torres
Using Powershell to Extract .dtsx files, search them and then replace the required string.

This code will help you extract SSIS packages from SQL 2008 and then Powershell code will help you do the replace a specific string on each package that has that string.

The code below is used to extract .dtsx files, search them and then replace the required string using PowerShell.  This code works on Windows Server 2008, however, other versions may need the query used to be modified since in every version the XML has moved to other locations or tables.

The code below only works for 2008. In order to work for other versions, the select query has to be modified to comply with the version being used.

Step 1 Package Extraction

Param($SQLInstance="ENTERSERVER/InstanceName") #Server\Instance  #MyDatabaseName

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100-ErrorActionSilentlyContinue
add-pssnapin sqlservercmdletsnapin100-ErrorActionSilentlyContinue
$Packages =Invoke-Sqlcmd-MaxCharLength10000000-ServerInstance$SQLInstance-Query"WITH cte AS (
    SELECT    cast(foldername as varchar(max)) as folderpath, folderid
    FROM    msdb..sysssispackagefolders
    WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'
    Select '\',folderid     
    FROM msdb..sysssispackages dts
    Where folderid = '00000000-0000-0000-0000-000000000000'
    SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid
    FROM    msdb..sysssispackagefolders f
    INNER JOIN cte c        ON    c.folderid = f.parentfolderid
SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
FROM    cte c
INNER    JOIN msdb..sysssispackages p    ON    c.folderid = p.folderid
WHERE    c.folderpath NOT LIKE 'Data Collector%'"

Foreach ($pkgin$Packages)
    $pkg.pkg |Out-File-Force-encodingascii-FilePath"$fullfolderPath\$pkgName.dtsx"

Step 2 Find Packages with a specific string

Edit $SearchString to contain the string you are searching for. You may need to create a folder on your desktop called “DataDump_PS”. This script will find all the packages with $SearchString and display in a popup window as well as saving the list to the DataDump_PS folder.

$a =Get-Date
write-Host $a

$RootString="c:\temp\$SQLInstance\$folderPath\" #Path to extracted packages
$SearchString ="" #String your looking for

Write-host $SearchString
$FileName =Get-ChildItem$RootString-recurse|Select-String-pattern$SearchString|grouppath|selectname

$FileName |Out-GridView

$SearchString =$SearchString.Replace("[","_")
$SearchString =$SearchString.Replace("]","_")
$SearchString =$SearchString.Replace("(","_")
$SearchString =$SearchString.Replace(")","_")

$FileName |Out-file-width300-filepath"C:\Users\SomeUser\Desktop\DataDumps_PS\$SearchString.txt"

$a =Get-Date
write-Host $a 

Step 3

Once you have all the packages identified from step 2. I would place them is a folder of their own. You don’t have to do this, I do it for piece of mind to be able to go back and spot check packages and make sure my replacement value was actually done.

This piece of code will replace files of type dtsx in a folder

In $RootString place the path to files that need a find and replace

In $SearchString place the string your looking for to replace

In $Replacement  place the new string you like to take place

$RootString ="\\Servername\mydocs\SomeUser\My Documents\SSIS_Packages\" #Path to Identified packages
$SearchString ="" #Looking for
$Replacement ="MySQLServer"    #Replacement value

    gci -Path$RootString-Recurse-Filter*.dtsx|
    Where {$_-IS[IO.FileInfo]} |
    % {
        $txt =gc$_.FullName
        if($txt-match$SearchString ){
                sc $_.FullName ($txt-replace$SearchString,$Replacement )

Once your find and replace are complete, I would go back and spot check to make sure those places where the previous string existed now contain the new string. 

Author:Leo Torres
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free