Manage packages stored in the MSDB database with T-SQL

Last week we’ve performed a typical migration from a development server to a production server.

Both servers are running SQL Server 2008 R2 databases. I created a set of SSIS ETL packages and I wanted to move them to production environment too.

In our development environment we usually store the packages in the msdb database or in a package store. To administer the packages stored using any of these options, I connect to the Integration Services service using the Management Studio. In this way I can use the Management Studio to create, edit or delete packages and folders with only a few clicks.

Everything was migrated to production server. Then I tried to connect to the Integration Services using the Management Studio. Can you guess what our surprise was?

The production server is installed in a cluster node and Integration Services service is not a Cluster service. It could be configured in a cluster but Microsoft strongly recommends avoiding it because the disadvantages outweigh the advantages. Link to Microsoft Documentation.

The important thing was to realize that the Integration Services service is not needed to run or develop SSIS packages, but supports the administrative interface in SQL Server Management Studio for listing, starting, stopping, monitoring, importing, and exporting Integration Services packages.

I found this post also very interesting and it helped me with this issue: Recommended Post

After lost this capability I started to find another option to admin my packages and at the end I found this set of built-in stored procedures that made my life easy:

• msdb.dbo.sp_ssis_addfolder
• msdb.dbo.sp_ssis_listfolders
• msdb.dbo.sp_ssis_deletefolder
• msdb.dbo.sp_ssis_deletepackage
• msdb.dbo.sp_ssis_listpackages
• msdb.dbo.sp_ssis_renamefolder

Although the names of the procedures are self-explanatory it was hard for me to find useful documentation. I let you some examples:

/*Add a Folder*/

exec msdb.dbo.sp_ssis_addfolder

@parentfolderid = ‘00000000-0000-0000-0000-000000000000’

,@name = ‘My_Packages’;

/*List Folders*/

EXEC msdb.dbo.sp_ssis_listfolders ‘00000000-0000-0000-0000-000000000000′

/* folderid=’0EAB9DEE-2B77-4821-9C36-EE70A7C76A92′ that is the ID of the Folder*/

/*Delete Folder*/

EXEC msdb.dbo.sp_ssis_deletefolder @folderid=’0EAB9DEE-2B77-4821-9C36-EE70A7C76A92’

I hope you find my experience useful or at least it could give you some clues if you face a similar issue.

Advertisement