Understanding Stored Packages on Integration Services

Question

We have several SSIS packages (in a solution) saved in a directory on the server. The packages are run via a SQL Server Agent job, with 'File system' as the Package source. Packages are not in any way added to 'Integration Services' (the thing you can connect with through the "Connect" button in SSMS). This seems to work fine.

Now I'm trying to understand the practical use and workings of the Integration Services' Stored Packages. It is probably there for a good reason - but I'm not getting it yet (reading several SSIS topics didn't help).

If I open Integration Services and add an existing package to the MSDB folder in Stored Packages, it is added to the msdb System Database of my SQL Server instance. I can then query some information on it from sysssispackages or sysssispackagefolders, and run the package directly.

If I add an existing package to the File System folder in Stored Packages, nothing really seems to happen, except that it also allows me to run it by right-clicking and choosing 'Run Package'.

I feel like I'm missing the important stuff here. My questions are as follows:

1. What reasons are there for using Integration Services to store packages in addition to saving them as files on the server?
2. What exactly happens when you add a package to the 'File System' or 'MSDB' folders in the Integration Services 'Stored Packages' folder? What's the advantage?

Any pointers/insights very much appreciated!

Answer 1

Here are a few advantages/disadvantages of database stored packages vs file system stored packages:

File System

• OS-based file encryption and ACLs
• Easier direct access for viewing or editing the package
• Generally easier for the developer to manage (to change a package, just replace the file)

SQL Server

• Easier access by multiple individuals
• Benefits from database security, roles and Agent interaction
• Packages get backed up with normal database backup processes
• Generally easier for the DBA to manage (he will have the control of what happens with the packages in terms of changes, etc..)

In regards to you second question, a package is an XML file that SSIS can read and execute. On the file based deployment, the SSIS locates the package on your file-system and executes it, on the DB deployment the SSIS locates the package on an MSDB table and executes it. There are no differences in terms of performance whatsoever.

Answer 2

1. Backup. If packages are on the msdb database, when you backup the database, you backup your packages. Also they can take advantage of SQL Server security and they are not just laying around on a file system.

2. The advantage is that you dont have to worry about the file path, or that someone will delete the file or move the folder to another location. The package will always be on the the "same place".