SQL Server Integration Packages–The Pain of Storage

One of the largest pain points for me when Microsoft moved their ETL (Extract, Transform, and Load) technologies for SQL Server from DTS (Data Transformation Services) to SSIS (SQL Server Integration Services) has been their reduced editing functionality for packages saved in the MSDB database.

 As a DBA and not a developer, I have always been a fan of taking advantage of SQL Server’s built-in functionality to protect packages and their “secrets” (passwords for connecting to other systems, etc.) and to ensure that they get backed up along with the SQL Server system databases.  With the advent of SSIS, Microsoft has made this much more difficult to work with since there is no direct editing of packages stored in MSDB.  You end up having to export the package, edit it, and then re-import it into MSDB.  That is a pain in sensitive body parts for more than a couple of packages.

Developers advise us to use the file system to store our SSIS packages like they would store their VB or C# program code, projects, and solutions to work around these issues.  In my opinion, this has some significant disadvantages:

1.  To save secrets in the packages, you have to either encrypt the package with a password or with a user’s key.  If you use a password, that is one more thing to lose or forget, meaning no one has access to the secrets in the package or package source, or everyone does.  If you use a user’s key, then access to the secrets or package source is only available to that particular user.

2.  You cannot take advantage of SQL Server’s built-in roles in the MSDB database like db_ssisadmin to secure who has access to SSIS.  You are forced to rely on a combination of the security listed in Step 1 (user keys and package passwords) and NTFS file system security.  The introduction of having to worry about NTFS-level security adds yet another variable (and in large organizations, another team of administrators) to worry about.

Thankfully, someone has finally taken the time to write and make available an automated way to import packages to and export packages from MSDB.  The June 2009 issue of SQL Server Magazine has a reader solution that allows us to easily import and export SSIS packages from MSDB.  You can find the online version and code here:  http://www.sqlmag.com/Articles/ArticleID/101918/101918.html

Thank you Shaunt Khaldtiance and thank you SQL Server Magazine. 

Hope this helps someone else;


Leave a Reply

You must be logged in to post a comment.