Understanding Stored Integration Services Packages

Question

We have several SSIS packages (in the solution) stored in a directory on the server. Packages are launched using the SQL Server Agent job, and the file system is used as the package source. Packages are by no means added to Integration Services (something you can connect to through the Connect button in SSMS). This seems to be normal.

Now I'm trying to understand the practical use and operation of Stored Package Services. This probably happens for a good reason, but I'm not getting it yet (reading a few SSIS topics didn't help).

If I open Integration Services and add an existing package to the MSDB folder in Stored Packages, it will be added to the msdb system database of my SQL Server instance. Then I can request some information about this from sysssispackages or sysssispackagefolders and run the package directly.

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

I feel that important material is missing here. My questions are as follows:

• What are the reasons for using Integration Services to store packages in addition to saving them as files on the server?
• What exactly happens when you add a package to the "File System" or "MSDB" in the "Storage Packages" folder of Integration Services? What is the advantage?

Any pointers / ideas are greatly appreciated!

Answer 1

Good, so I misunderstood the SSIS package repository and saved the packages. Here is what I learned. First of all, "what you can contact with the Connect button in SSMS" (Connect → Integration Services ...) is called the SSIS package repository.

What exactly happens when a package is added to the File System or MSDB folders in the Integrated Services Stored Packages folder? What is the advantage?

File system

If you want to work with the File System folder in the SSIS package repository, save your packages in the default file system directory ( ...\Microsoft SQL Server\100\DTS\Packages ) or change the root folder for the file system to the directory you want use. (You can change the root by changing the default <StorePath>..\Packages</StorePath> in the MsDtsSrvr.ini.xml file, which can be found in ...\Microsoft SQL Server\100\DTS\Binn . Do not forget to restart Integration Services after you finish.) When you add a package to this directory, it will appear in the File System folder in the SSIS package repository. You can then run the package directly from the SSIS package repository or by using the SQL Server Agent job (by selecting SSIS Package Repository as the source of the package in the Job Step properties, and then selecting the package).

Editing packages is very simple: open the package in the file system directory, edit and save, and the new version will be instantly available through the SSIS package repository.

Benefits:

• Easy to deploy and troubleshoot packages.
• Packages are still available if the database engine is not available.

SQL Server / MSDB

If you want to use the msdb database to save your packages, you need to import each package into msdb through the SSIS package repository. Right-click the MSDB folder and select Import Package. This will save the package in the msdb database. After that, you do not need to save the source files of the .dtsx package.

Editing packages is a bit more complicated : you need to export the package, edit it and import the package again into the SSIS package repository. Or you can open a new project in BIDS, add a package by right-clicking the SSIS packages and selecting "Add an existing package with SQL Server", edit it and then import the package again into the SSIS package repository.

Benefits:

• Package security can be tightly configured using database security
• Packages will be copied when backing up the msdb database.
• Packages are stored in a central location.

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

So, why are you adding the package to the SSIS package repository, and not just starting it, as we did, directly referring to the package.dtsx file from the job properties window)? It depends on whether you need packages in the msdb database, you need a batch store, because there is no other way to support your packages. If you are using the file system, you may have a separate Development and Deployment directory, and all packages that are ready for deployment can be found through the SSIS package repository. In each case, SSIS package storage provides a convenient interface for your packages.

Thanks João Leal and Diego for your answers!

Answer 2

Here are some of the advantages / disadvantages of packages stored in the database and file system stored packages:

File system

• OS and ACL based file encryption
• Easier direct access to view or edit the package.
• As a rule, it’s easier for the developer to manage (change the package, just replace the file)

SQL Server

• Easier access for multiple people
• Benefits of Database Security, Roles, and Agent Interactions
• Packages receive backups with normal database backup processes.
• As a rule, the DBA administrator is easier to manage (he will control what happens to the packages in terms of changes, etc.).

Regarding the second question, a package is an XML file that SSIS can read and execute. When deploying based on files, SSIS finds the package in your file system and executes it; when deploying the database, SSIS finds the package in the MSDB table and executes it. There are no differences in performance.

Answer 3

• Backup. If the packages are in the msdb database, when you back up the database, you back up your packages. In addition, they can take advantage of SQL Server's security benefits, and they don't just fit in the file system.

• The advantage is that you don’t have to worry about the path to the file or that someone will delete the file or move the folder to another location. The package will always be in the same place.

Answer 4

This explains the differences better than social.msdn.microsoft.com

File system:

I am sure that you know exactly what the FileSystem option is, but to fill out this section: you can save the package to a physical location on your hard drive or to any shared folder with this option, and you must provide the full path to the saved 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 share them in any folder you want. these folders are virtual folders that are listed in the sysssispackagefolders table. therefore, with this option, the ssis package will not be stored as a physical file. it will be stored in the msdb database.

SSIS Package Store:

this option is actually not a real deployment option. What for? because it’s just a redirect step. I mean, if you select this option in BIDS, when you try to save a copy of the package ... you will see a tree with the parent "SSIS packages" and two children: "MSDB" and "File System". if you select "MSDB", the package will be saved in the msdb database in the same way as the sql server parameter (the previous option that is described). If you select "File system", the ssis package will be stored as a physical file with the extension .dtsx, but in the folder at this address: \ Microsoft SQL Server \ 100 \ DTS \ Packages. it looks like a file system option, but you can’t save the package anywhere, you can save it to a predefined physical location and, of course, you can create subfolders there, but you cannot cross the parent to save the package by any other than the predefined ones.