SQLCastle: 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.

For more information - http://msdn.microsoft.com/en-IN/library/ms189477.aspx

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 )

For more information - http://technet.microsoft.com/en-us/library/ms181582.aspx

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.

For more information - http://msdn.microsoft.com/en-us/library/ms139818.aspx

http://www.bimonkey.com/2009/06/the-export-column-transformation/

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 –