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 ModulesSSIS 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 FilesMicrosoft SQL Server100DTSPackages*" -destination "msdbsqlpsx" -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:computernamesql1" -destination 'c:UsersPublicbinSSIS' -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:computernamesql1) 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:computernamesql1"  | remove-isitem -WhatIf
 get-isitem '' 'msdb' "$env:computernamesql1" | ?{$_.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(msdbsqlpsxsqlpsx1,Z003)".
What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdbsqlpsxsqlpsx2,Z003)".
What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdbsqlpsxsqlpsx3,Z003)".
...
What if: Performing operation "Remove-ISItem" on Target "RemoveFolderFromDtsServer(msdbSQLPSX,Z003)".

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

1
2
 get-isitem 'sqlpsx' 'msdb' "$env:computernamesql1" | remove-isitem
 get-isitem '' 'msdb' "$env:computernamesql1" | ?{$_.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:

{ 22 comments… add one }

  • Jamie Thomson February 3, 2011 at 7:53 am

    Reply edit
    • Chad Miller February 8, 2011 at 8:06 pm

      Thanks. As soon as I have some time I’m planning on building an SSIS provider so you can navigate and SSIS “drive” just as you would with SQLServer using sqlps. With the concept of packages and folders, SSIS fits well into the provider model.

      Reply edit
  • Conor November 6, 2012 at 5:07 pm

    This is exactly what I need, but being a noob I have no idea how to use this. I have downloaded the SQLPSX but beyond that, I don’t know how to start using your examples. In my job I find myself needing to export SSIS packages from SQL 2005 and import them into SQL 2008(R2). How do I find the query editor window you are using above?

    Reply edit
  • Conor November 8, 2012 at 12:02 pm

    nevermind, I found it.
    but when I ran the very first example above, new-isitem I am getting the following error:

    New-ISItem : Cannot bind argument to parameter ‘serverName’ because it is an empty string.
    At line:2 char:11
    + new-isitem <<<< 'msdb' 'sqlpsx' $env:HBI_2003
    + CategoryInfo : InvalidData: (:) [New-ISItem], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,New-ISItem

    Reply edit
    • Conor November 8, 2012 at 12:33 pm

      This is also on a 2005 server so to comment out the 2008 section I simply put a # in front of the ‘add-type -AssemblyName……Version=10.0.0.0′ line. Is that correct?

      Reply edit
      • Chad Miller November 9, 2012 at 2:18 pm

        You shouldn’t need comment out to use 2005, because of the if/else logic at the top of the SSIS.psm1. The module is text file so you can ready it. To use 2005 you run:

        import-module SSIS -ArgumentList 2005

        One very important thing to keep in mind. In .NET you can’t unload an assembly so if you load the 2008/2008 R2 assembly in a Powershell session you’ll need open a new Powershell Window to load 2005 assembly. The reverse applies also.

        Reply edit
    • Chad Miller November 9, 2012 at 2:12 pm

      My guess is that $env:HBI_2003 is null, as the error messages indicates.

      What do you get when you run $env:HBI_2003?

      Reply edit
      • Conor November 9, 2012 at 2:49 pm

        it comes back with
        PS C:Documents and SettingsAdministrator> $env:hbi2003

        I updated the name and took the underscore out, in case you were wondering why it looks different now. But it still gives me the same error.

        Reply edit
        • Chad Miller November 9, 2012 at 3:52 pm

          So you have an environmental variable hbi2003?

          In any case new-isitem expects 3 parameters and it isn’t receiving the server name. I would suggest as a test, don’t use $env:hbi2003 and instead specify the servername directly:

          new-isitem ‘msdb’ ‘sqlpsx’ ‘YourServerName’

          Reply edit
      • Conor November 9, 2012 at 4:16 pm

        when you say “run $env:HBI_2003″ do you just mean simply typing $env:HBI_2003 into line 1 and hitting play?

        Reply edit
        • Chad Miller November 9, 2012 at 5:43 pm

          It would be the run script button which looks like a play button if you’re in the editor. You should see a value.

          $env: is a prefix for environmental variable. If you’re using cmd you’d use “set” to see all environment variable or echo %envname%. In Powershell the environnmental variables are accessed using $env: instead of %name%.

          Reply edit
          • Conor November 12, 2012 at 10:54 am

            $env:COMPUTERNAME
            this returns ‘HBI2003′. However if I use HBI2003 in the new-isitem command, I get “New-ISItem : Cannot bind argument to parameter ‘value’ because it is an empty string.”

            I have confirmed this is my server name, I must be missing something. Do I need to do anything special with the Path or tell the SSIS.psm1 file where to get the servername from? I am sorry if I am being a pest, just would really like to get this working.

            edit
          • Chad Miller November 12, 2012 at 11:06 am

            So you’ve started a new Powershell.exe? It’s complaining about the value parameter being null or empty and stated this was 2005 server? Let’s try this.

            import-module SSIS -ArgumentList 2005
            new-isitem -path 'msdb' -value sqlpsx -serverName HBI2003

            edit
  • Conor November 8, 2012 at 12:09 pm

    By the way, thank you so much for this amazing tool. I am slowly figuring it out.

    Reply edit
  • Conor November 12, 2012 at 3:56 pm

    THAT DID IT!!!!!!!!!! I was able to create the folder and Import SSIS pacakges. I have also been able to Export them.
    I did just have one other question. The way this is setup the SSIS packages need to live under the sqlpsx folder under msdb. More often than not, I see the packages just under the root of msdb. how would I modify the command line to leave out the ‘sqlpsx’ value so it’s just looking under the root of msdb?

    I can’t thank you enough for taking the time to help me with this!

    Reply edit
    • Chad Miller November 12, 2012 at 4:12 pm

      No problem, its usually better to use named instead of positional parameters anyways. I should update the documentation for new-isitem accordingly.

      The sqlpsx folder is just something I use for an example if you want to copy to the root msdb folder, just use msdb instead of ‘msdbsqlpsx’ here’s an example:

      Copy-ISItemFileToSQL C:UsersPublicbinSSISsqlpsx1.dtsx -destination msdb -destinationServer HBI2003

      Reply edit
      • Conor November 13, 2012 at 9:31 am

        I think I should have been more specific. I am able to export the SSIS packages when they live under the sqlpsx folder. However, if they are just under the root of msdb, they don’t get exported.

        Keeping that in mind, what value should I give the “-path” parameter if the packages are found just under the root of msdb?

        copy-isitemsqltofile -path ‘sqlpsx’ -topLevelFolder ‘msdb’ -serverName “$env:computername” -destination ‘C:SSISSSISExport’ -recurse

        Thanks!

        Reply edit
  • Conor November 14, 2012 at 4:17 pm

    Chad,

    I can’t thank you enough for all the time you took to walk me through this. I have it all setup and it works perfectly! This is a HUGE lifesaver.

    Thanks again,

    Conor

    Reply edit
  • Paul Hernandez March 27, 2013 at 6:57 am

    Hi Chad, thanks so much for your amazing work.
    I just want to point out, that I had to slightly modify the input parameters for:
    .- copy-isitemfiletosql : -destination “msdbsqlpsx” -> -destination “msdb\sqlpsx”
    .- copy-isitemsqltofile: -topLevelFolder ‘msdb’ -> -topLevelFolder ‘msdb\’

    I don’t know exactly why this “\” problem, maybe something related to my specific configuration but I´m just guessing. Anyway it works.

    Kind Regard, Paul

    Reply edit

Leave a Comment


7 + = eleven

%d bloggers like this: