Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

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:

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

We can see the folder in SSMS:

ImportSSIS1

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:

ImportSSIS2

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:

ImportSSIS3

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:

ImportSSIS4

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:

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

Related Posts:

Comments

Posted by Michael Robey on 15 February 2012

This script I presented below works in SQL 2008 but not on SQL 2005 I assume because of the -path entry being null since it does not like a \.  Do you have any ideas on how to script SSIS packages from SQL 2005?

# Configuration data

[string] $server   = "SERVER2005";          # SQL Server Instance

#[string] $database = "NoDatabaseSelected";      # Database with the tables to script out.

[string] $folder   = "C:\LOCALFOLDER";          # Path to export to

#param($server, $folder)

#instantiate the FileSystemObject

$objFSO = New-Object -ComObject Scripting.FileSystemObject

import-module SSIS

Write-Output ((Get-Date -format yyyy-MM-dd_HH-mm-ss) + ": *** SSIS Started ***");

#Export SSIS Package  

$SSISFilePath = $folder + "\" + $Server + "\_SSIS"

copy-isitemsqltofile  -path '' -topLevelFolder 'msdb' -serverName $Server -destination $SSISFilePath -recurse

Leave a Comment

Please register or log in to leave a comment.