Blog Post

Importing and Exporting SSIS Packages Using PowerShell


SQL Server PowerShell Extensions (SQLPSX) includes a set of function for working with SSIS which among other things allow you to import and export SSIS packages between the file system and msdb. The functionality is best illustrated by looking a few examples.

Creating an SSIS folder

Note: The SSIS module supports SQL 2005 through 2008 R2. By default the module is setup to use the 2008  or 2008 R2 assembly, to switch to 2005, comment/uncomment the appropriate assembly at the top of SSIS.psm1 file in the \Modules\SSIS folder. Once loaded an assembly can’t be unloaded (.NET thing), so you’ll need to start a new PowerShell host to switch between 2005 and 2008.

Use the new-isitem function to create a folder. The following example imports the SSIS module and creates a folder called sqlpsx off of the root \msdb folder:

import-module SSIS
new-isitem '\msdb' 'sqlpsx' $env:computername

We can see the folder in SSMS:


Importing SSIS Packages to MSDB

Having created a folder, next I want to import SSIS packages on the file system  to MSDB. In addition as part of the copy process I want to change the location where my SQL Server table-based Package Configuration points:

File System dtsx files:


I’ll use the copy-isitemfiletosql function…

copy-isitemfiletosql -path "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\*" -destination "msdb\sqlpsx" -destinationServer "$env:computername" -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"}

Note: The SSIS copy-* functions include a progress bar indicator:


Exporting SSIS Packages from MSDB

Now that I have SSIS packaged stored in MSDB, I’ll copy them back to the file system using the copy-isitemsqltofile function…

copy-isitemsqltofile -path '\sqlpsx' -topLevelFolder 'msdb' -serverName "$env:computername\sql1" -destination 'c:\Users\Public\bin\SSIS' -recurse

Looking at the file system we see the dtsx files have been created:


Note: The API ManagedDTS has some inconsistencies in usage, so the SQL Server instance ($env:computername\sql1) instead of just the computer name ($env:computername) is needed.

Removing SSIS Packages and Folders from MSDB

Note: Like any delete operation be careful!

This isn’t a common operation, but for completeness I’ll remove the SSIS packages and folders I created. As a safety measure the remove and copy  functions support the standard PowerShell WhatIf and Confirm parameters, so first I’ll run the command with –WhatIf:

 get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1"  | remove-isitem -WhatIf
 get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$ -like "sqlpsx*"} | remove-isitem -WhatIf

This produces the following output:

What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx1,Z003)".
What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx2,Z003)".
What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx3,Z003)".
What if: Performing operation "Remove-ISItem" on Target "RemoveFolderFromDtsServer(msdb\SQLPSX,Z003)".

Satisfied with the results I’ll go ahead and remove the packages and folder:

 get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1" | remove-isitem
 get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$ -like "sqlpsx*"} | remove-isitem


Including the functions demonstrated in this post the SQLPSX SSIS module contains the following functions:

In addition to the online help, each function implement get-help with examples.

Related Posts:


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating