Tuesday, 19 May 2015

SSIS - Way to export all the packages from the Integration services

On several occasions, my friends or colleagues have asked me that why there is not a way to extract multiple packages @ same time from integration services. Well seriously, Why ? Microsoft whyyyyyy ?

However, as we all know for every problem there exist a solution (IMHO this is true for at least Microsoft technologies.. if solution does not work the RESTART will :) ) and mind you, that solution would be just around the corner, looking at you.. teasing you...Waiting for you to notice it (Just like girls, when they've got something new )… And when you notice it... all you (in girls case they) remember is the time you wasted to notice it. :)

So how do we export all the SSIS packages from the MSDB (or any other) folder to some location on the hard disk? Well use SSIS package to get this done… I am not kidding you? as Bizzar as it may sound but SSIS has one transformation which can be used to export the multiple packages in one go. .. Let me show you…

All you need is, to understand 2 system tables and one data flow transform.. The table names are sysssispackagefolders and sysssispackages  and the transform that is crucial here is Export Column transform.  

sysssispackagefolders – This table stores the information of the folders and their hierarchy in the integration services. It has the 3 columns 
folderid – GUID field unique for each folder 
parentfolderid  - GUID of the parent folder, is NULL for the MSDB as it’s a parent folder for all foldername column gives you the name for the folder.

sysssispackages  - This table stores the one row per package which are stored into SQL server. It has many columns out of which only 3 are important for our demo purpose and they are 
name – name of the package
folderid – GUID of the folder in which package was imported 
packagedata – this one contains the package definition but the data type of this column is image data. ( which is stored in the binary format )

 As we all know .dtsx file is nothing but the XML in the background. Therefore, if we could convert this image data into the XML we would be able to see what that package is up-to. Using SQL it is pretty straight forward.. We know that the image data is stored in the binary format then we should be good to cast image data type as varbinary(max) which we will do first, followed by converting varbinary(max)  to XML.

SELECT CAST(CAST(packagedata AS VARBINARY(MAX)) as xml)  as PkgXML
FROM MSDB..sysssispackages

You have to copy -paste this xml into a notepad and then save that file as dtsx...  and there you have your package!!. We want to achieve this using SSIS though, how about using Export Column transformation.

Export Column Transformation – It exports the binary data to the file. The data that we want to export using this transform must have DT_TEXT, DT_NTEXT or DT_IMAGE data type.

There is not much to configure in this transformation. All we have to do is, specify column name which has to be exported, path where that file should be created (please note that this cannot be specified explicitly. It has to a one of the column names) and way you want your file to create. 

Package :

All your package needs is one data flow component and 3 components on the data flow side …

DataFlow Components – 

Oledb Source – To query and fetch the packagedata  column from sysssispackages
Derived column – To form the path where packages will be created
Export column – To export the binary data of packagedata column to the create dtsx file out of it

Oledb source Query –

select * from msdb..sysssispackages
where folderid = '00000000-0000-0000-0000-000000000000' -- parent folder id

This query will give me details of all the packages imported in MSDB folder. Here folderid specified is the GUID of the MSDB database (can be confirmed using sysssispackagefolders table).

Derived Column - 


We have added new column Path to the data flow using derived column transform. In its expression name is column from the Oledb data source, which gives us the name of the package.

Exported Column – 


Once all the setting is done. Please execute this package it will export the packages @ the specified location.

Overall, your entire data flow task should look like this


The number of rows (packages) will depend on the number of packages exist in the MSDB folder  of the integration services.

As you can see, exported packages @ the path, which we had specified


1 comment:

  1. This was of great help, tried so many different ways, but this worked like a charm for my sql2005 retirement project.