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:
1 2 | 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…
1 | 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…
1 | 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:
1 2 | get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1" | remove-isitem -WhatIf get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$_.name -like "sqlpsx*"} | remove-isitem -WhatIf |
This produces the following output:
1 2 3 4 5 | 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:
1 2 | get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1" | remove-isitem get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$_.name -like "sqlpsx*"} | remove-isitem |
Summary
Including the functions demonstrated in this post the SQLPSX SSIS module contains the following functions:
- Copy-ISItemSQLToSQL
- Copy-ISItemSQLToFile
- Copy-ISItemFileToSQL
- Get-ISData
- Get-ISItem
- Get-ISPackage
- Get-ISRunningPackage
- Get-ISSqlConfigurationItem
- New-ISApplication
- New-ISItem
- Remove-ISItem
- Rename-ISItem
- Set-ISConnectionString
- Set-ISPackage
- Test-ISPath
In addition to the online help, each function implement get-help with examples.
