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

This one explain the differences better social.msdn.microsoft.com

File System:

I'm sure you know exactly what the FileSystem option is, but to complete this section: you can save the package on a physical location on hard drive or any shared folder with this option, and you should provide a full qualified path to stored package in the FileSystem option.

SQL Server:

with this option, SSIS packages will be stored in the msdb database, in the sysssispackages table. you can separate them in any folder you want. these folders are virtual folders, which listed in the sysssispackagefolders table. so with this option ssis package will not store as physical file. it will store in the msdb database.

SSIS Package Store:

this option is not actually a real option for deployment. why? because this is a redirection step only. what I mean is that if you choose this option in the BIDS when you try to save a copy of package ... , you will see a tree with a parent "SSIS packages" and two child : "MSDB" and "File System". if you choose the "MSDB" the package will save under msdb database same as the sql server option ( previous option which described ).if you choose the "File System", the ssis package will store as a physical file with .dtsx extension but in a folder in this address: \Microsoft SQL Server\100\DTS\Packages . this is like file system option but you can not store package at any location , you can store it under a predefined physical location and of course you can create subfolders there, but you can not cross the parent to save package any other place than predefined.

Answer 2

Okay, so I misunderstood a lot about the SSIS Package Store and saving packages. Here is what I learned. First of all, 'the thing you can connect with through the "Connect" button in SSMS' (Connect -> Integration Services...) is called the SSIS Package Store.

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?

File System

If you want to work with the File System folder in the SSIS Package Store, save your packages to the default File System directory (...\Microsoft SQL Server\100\DTS\Packages) or change the root folder for File System to the directory you want to use. (You can change the root by changing the default value of <StorePath>..\Packages</StorePath> in the MsDtsSrvr.ini.xml file, which can be found in the ...\Microsoft SQL Server\100\DTS\Binn directory. Don't forget to restart the Integration Services service after you're done.) When you add a package to this directory, it will appear in the File System folder in the SSIS Package Store. You can then run the package directly from the SSIS Package Store or through an SQL Server Agent job (by choosing SSIS Package Store as the Package Source in the Job Step Properties and then selecting the package).

Editing packages is easy: open the package in the File System directory, edit and save, and the new version will be instantly available through the SSIS Package Store.

Advantages:

• Deployment and troubleshooting of packages is easy
• Packages are still available when the database engine is down

SQL Server / MSDB

If you want to rely on the msdb database to save your packages, you have to import each package into msdb via the SSIS Package Store. Right-click on the MSDB folder and choose Import Package. This will save the package to the msdb database. You do not need to save the original .dtsx package files afterwards.

Editing packages is a little harder: you have to export the package, edit it and import the package again in SSIS Package Store. Or you can open a new project in BIDS, add the package by right-clicking SSIS Packages and choosing Add Existing Package from SQL Server, edit it and then import the package again in SSIS Package Store.

Advantages:

• Security of packages can be tightly configured through database security
• Packages will be backed up as the msdb database is backed up
• Packages are stored in a central place

What reasons are there for using Integration Services to store packages in addition to saving them as files on the server?

So why would you add a package to the SSIS Package Store and not just run it like we did, by directly referencing to a package.dtsx file from the Job Step Properties window)? It depends: if you want your packages in the msdb database, you need the Package Store, because there is no other way to maintain your packages. If you use the File System, might be that you have a separate 'Development' and 'Deployment' directory and all packages that are ready for deployment can be found through the SSIS Package Store. In each case, the SSIS Package Store provides an easy interface to your packages.

Thanks to João Leal and Diego for your answers!

Answer 3

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 4

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".