≡ Menu

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:

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 FilesMicrosoft SQL Server100DTSPackages*" -destination "msdbsqlpsx" -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:computernamesql1" -destination 'c:UsersPublicbinSSIS' -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: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:

 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:

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:

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

Comments on this entry are closed.

  • Jamie Thomson February 3, 2011, 7:53 am
    • Chad Miller February 8, 2011, 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.

  • Conor November 6, 2012, 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?

    • Chad Miller November 6, 2012, 10:14 pm

      The SSIS module includes documented help, for example you can run:
      import-module ssis
      help copy-isitemsqltofile -full

      Also my blog has a couple of examples using the SSIS module:


      However the task you’re trying automate can’t be done using the SSIS module or dtutil.exe. To upgrade SSIS packages you’ll need to open the 2005 SSIS packages and save them in BIDS (i.e. Visual Studio) 2008 R2. You could save them to an intermediate dtsx file using the copy-isitemsqltofile or dtutil, but the upgrade unfortunately is still manual through VS open and save.

      I’m not sure what you mean by query editor window. If you mean styling the code in my blog I’m using WordPress WP-Syntax.

  • Conor November 8, 2012, 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

    • Conor November 8, 2012, 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=’ line. Is that correct?

      • Chad Miller November 9, 2012, 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.

    • Chad Miller November 9, 2012, 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?

      • Conor November 9, 2012, 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.

        • Chad Miller November 9, 2012, 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’

      • Conor November 9, 2012, 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?

        • Chad Miller November 9, 2012, 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%.

          • Conor November 12, 2012, 10:54 am

            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.

          • Chad Miller November 12, 2012, 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

  • Conor November 8, 2012, 12:09 pm

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

  • Conor November 12, 2012, 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!

    • Chad Miller November 12, 2012, 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

      • Conor November 13, 2012, 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


        • Chad Miller November 13, 2012, 9:41 am

          -path ” everything else should be the same.

          There’s an example of getting but not copying from the root using the get-isitem function, same concept though.

          help get-isitem -examples

  • Conor November 14, 2012, 4:17 pm


    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,


    • Chad Miller November 15, 2012, 1:17 pm

      You’re welcome and thanks for sticking with it.

  • Paul Hernandez March 27, 2013, 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

%d bloggers like this: