SSIS – Export all SSIS packages from msdb

So, in response to Jamie Thomson’s article on extracting all SSIS packages from msdb, I also wanted to show how this can be done from within SSIS itself. It’s very straightforward, and uses a data flow component you may not be familiar with: Export Column.

First, setup four package level variables:
FilePath – string
SSISFolderFilePath – string
SSISFolderName – string
SSISFolders – object

FilePath should be set to the location where you want to store the packages. Include the trailing slash, and ensure that the location exists.
SSISFolderFilePath should be set to evaluate an expression (EvaluateAsExpression = TRUE), and its expression should be:
@[User::FilePath] + @[User::SSISFolderName]

Then create a connection manager object that points to your MSDB database where you want to extract from. If you want this to be dynamic, perhaps to archive ALL SSIS packages in your company, then this could be made a dynamic connection manager object (by using expressions) and you iterate over each server. For now, we’re only going to use one server.

Once these are setup, the rest is point and click, pretty much. Add an Execute SQL Task to the control flow, and configure it so that it returns a full resultset. See below.

Execute SQL Task

Execute SQL Task 2

The query for the Execute SQL Task is:

SELECT foldername
  FROM dbo.sysssispackagefolders folders
 WHERE EXISTS (SELECT NULL 
                 FROM dbo.sysssispackages pkg
                WHERE pkg.folderid = folders.folderid)

*Note – the above SQL only assumes a flat directory structure. If you have subdirectories in MSDB, you can use a recursive CTE to create a hierarchy resultset.

With the Execute SQL Task set up, you’ll have to shred the results using a Foreach Loop Container. Inside the Foreach Loop Container, we will want to use a File System Task to create directories for our packages if they do not already exist. The configuration for that is below:

File System Task

Next, we have to create a data flow and attach it to the Foreach Loop Container. The full control flow should resemble the next screenshot.

Control Flow diagram

In the data flow, we have three components – OLE DB Source, Derived Column, and the Export Column transformation. Connect the OLE DB source to your connection manager object, and use the following query against MSDB:

SELECT folder.foldername, 
       pkg.name, 
       pkg.packagedata
  FROM dbo.sysssispackages pkg
  JOIN dbo.sysssispackagefolders folder
    ON pkg.folderid = folder.folderid

Then, configure the Derived Column component so that it creates one new column, FullFilePath. The following is the expression to use:

(DT_WSTR,500)(@[User::FilePath] + foldername + "\\" + name + ".dtsx")

Then configure the Export Column transformation as the following screenshot illustrates:

Export Column configuration

The final data flow should look like the following:

Data flow diagram

The only thing left to do is execute the package. Navigate to the location defined by the FilePath package variable and you should see a directory structure resembling that in MSDB with all of the SSIS packages saved.

File contents

If you wish to download the full SSIS package, here you go: http://www.ssistalk.com/ExportSSISPackages.dtsx

21 thoughts on “SSIS – Export all SSIS packages from msdb

  • joe positive

    Very nice! Thanks.

  • Sudarshan

    Really helpful. Thank you Phil 🙂

  • Artur

    Great article.
    Do you know if we can do the same with RDL files?

  • Keller

    Thanks so much for this package. Been tearing my hair out trying to do something that should be simple…

  • philips lumea review

    You will findYou will findYou will getYou’ll locate some attention-getting closing dates this article however I have no idea basically see them all heart in order to be able toto have the ability to heart. There might be some validity but I’ll take hold opinion until I think of it further. Great article , thanks as well asadditionally we would like extra! Added be able toto have your ability FeedBurner as effectively

  • Chat

    This was grreat and great info. Obvious question arises is whether Import Column can be used to load files back to the server same way or not. Please answer.
    For 2008, i guess it’s all about inserting rows into sysssispackages discounting folder hierarchy.
    I am ofcourse trying it out myself too but not sure when and ofcourse not sure if it’d work.

  • Kandarp Patel

    This is really helpfull article. My concern is that i am exporting this for other users, how can I set protection level to dont save sensitive on the go?

  • hml

    The export doesn’t seem to export the package configuration where we set the server names. Is there a way to export this with the SSIS package data?

  • TechTnT

    This is a very handy package and does precisely what I needed to do. Thank you for the step by step process… it was a cinch.

  • Riya

    HI, I’ve sub directory in MSDB, what will be the query for this?

  • DJDENALI_SA

    Thanks the package worked like magic, but now how do one use it with subdirectories on msdb?

  • DJDENALI_SA

    Thanks a million

  • Luis Paez

    thank you very much for sharing this, but I agree that if you could also put the query for use with an msdb with subdirectories it would be great!

  • sulekha

    the package worked like a charm!! Great job. Thanks.

  • JOSE GREGORIO LASTRE

    thanks!!! article very goooooood

  • Saundrah

    Query for subdirectories

    WITH cte AS (
    SELECT cast(foldername as varchar(max)) as folderpath, folderid
    FROM msdb..sysssispackagefolders
    WHERE parentfolderid = ‘00000000-0000-0000-0000-000000000000’
    UNION ALL
    SELECT cast(c.folderpath + ‘\’ + f.foldername as varchar(max)), f.folderid
    FROM msdb..sysssispackagefolders f
    INNER JOIN cte c ON c.folderid = f.parentfolderid
    )
    SELECT c.folderpath
    FROM cte c
    INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
    WHERE c.folderpath NOT LIKE ‘Data Collector%’

  • Saundrah

    Oops.. left a couple fields out of final SELECT.

    SELECT c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
    FROM cte c
    INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid
    WHERE c.folderpath NOT LIKE ‘Data Collector%’

  • masud

    I have modified @sundar’s query to include the packages in the root folder:

    WITH CTE_export_packages AS
    (
    SELECT CAST(foldername AS varchar(MAX)) as folderpath, folderid
    FROM msdb..sysssispackagefolders
    WHERE parentfolderid=’00000000-0000-0000-0000-000000000000′
    –AND f.foldername NOT LIKE ‘Data Collector’

    UNION ALL

    SELECT CAST(c.folderpath + ‘\’ + f.foldername AS varchar(MAX)), f.folderid
    FROM msdb..sysssispackagefolders AS F
    INNER JOIN CTE_export_packages as c
    ON c.folderid=f.parentfolderid
    )
    SELECT c.folderpath, p.name FROM CTE_export_packages AS c
    INNER JOIN msdb..sysssispackages p
    ON c.folderid=p.folderid
    WHERE c.folderpath NOT LIKE ‘Data Collector’

    UNION ALL

    SELECT f.foldername, p.name
    FROM msdb..sysssispackages AS p
    INNER JOIN msdb..sysssispackagefolders AS f
    ON p.folderid=f.folderid
    WHERE foldername NOT LIKE ‘Data Collector’
    AND foldername=”

  • Opan20

    Hi – This is Great! Work like a Charm.

    Question though, Do you have a way to import this to a different server all at once?

    Basically, if I want to migrate these as bulk, how can it be done.

    Thanks.

  • KayKay

    I am trying to do the same and the tree structure is: MSDB\\.dtsx>

    The “\\” on the expression (DT_WSTR,500)(@[User::FilePath] + foldername + “\\” + name + “.dtsx”) is throwing the below error!
    What can be the fix?

    Errors:
    [ExpCol Save Package [16]] Error: The file name “D:\SSIS_Backup\SSIS_Packages\SQL_Currency\SQLCurrency.dtsx” is not valid. The file name is a device or contains invalid characters.

    [ExpCol Save Package [16]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The “component “ExpCol Save Package” (16)” failed because error code 0xC020207F occurred, and the error row disposition on “input column “FullFilePath” (45)” specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “ExpCol Save Package” (16) failed with error code 0xC0209029 while processing input “Export Column Input” (17). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

  • KayKay

    see the error message for the exact package tree structure. Its misspelled in the first line.

Comments are closed.