SSIS Package Store vs. MSDB

Question

When setting up a SQL Agent job, there are options for choosing, among others, "SQL Server" or "SSIS Package Store". If I choose "SSIS Package Store" and browse to MSDB instead of File System, I can choose SSIS packages stored in sysssispackages.

However, the documentation for the /DTS switch, which SSMS automatically chooses when selecting SSIS Package Store, appears to be only for packages stored on the file system.

I've also noticed that if the user executing the job doesn't have file system rights, the job will fail when choosing SSIS Package Store --> MSDB --> Package with an "Access is denied" error or "~Object doesn't exist". Since the package doesn't exist on the file system though, why does execution work when choosing SQL Server and fail when using SSIS Package Store? Is dtexec actually looking for a dtsx file on the file system even when MSDB is chosen? I have, likely a false, understanding that SQL Server and SSIS Package Store --> MSDB are the same thing when referencing the Server on which the agent is running.

I've done some research and I can't find a clear distinction of the differences, if any, between using SQL Server and SSIS Package Store when selecting from the MSDB folder.

Can anyone shed some light on the differences?

Solution

There are 2 locations to store SSIS packages: The file system or SQL Server. The rest is semantics.

File System

You can use the SSIS Package Store which is nothing but a well known location in the installation location.

%Program Files%\Microsoft SQL Server\{Version}\DTS\Packages

Or you can pick anywhere on the file system you like. If you go this route, then you'll need to ensure the SQL Agent account, or the credentialed proxies or, if you running packages from xp_cmdshell the SQL Server Service Account has access to that location.

The only advantage, if you want to call it that, of using the Package Store (i.e. the folder I mentioned) is you can use the Integration Services management tool that exists in SSMS (by connecting to Integration Services instead of database engine).

However that has a lot of pitfalls such as not being able to handle multiple instances, packages only run in 64 bit mode, no access to proxy accounts, etc. You shouldn't run packages from SSMS anyway.

SQL Server

If memory serves correct

• 2005 - stored in msdb.dbo.sysdtspackages90
• 2008 - stored in msdb.dbo.sysssispackages (I seem to recall 2008 RTM using a different table, or reused the 90 table but that got patched out)
• 2008 R2 - stored in msdb.dbo.sysssispackages
• 2012 (package deployment model) - stored in msdb.dbo.sysssispackages
• 2012 (project deployment model) - stored in SSISDB.catalog.packages^*
• 2014 (package deployment model) - stored in msdb.dbo.sysssispackages
• 2014 (project deployment model) - stored in SSISDB.catalog.packages^*

^*With the project deployment model, packages are "compiled" (zipped with a manifest) into a .ispac which is stored into the bowels of the SSISDB.internals.* tables.

Wrapup

Ultimately, where you store your packages does not affect your ability to run them. You can run packages using DTEXEC, SQL Agent or custom .NET code. The choice of storing packages is primarily dependent upon your management style.

References

• Package Management

Comments

[PseudoToad]

Thank Bill. If I put my package in integration services under the MSDB node and call that package using SQL Agent, I can select either SSIS Package Store or SQL Server from the list. When I use SSIS Package Store, the path to the package is MSDB/blah but when I use SQL Server it is /blah. I've also noticed that Package Store uses /DTS switch and the other /SQL. I'm surprised that I can find the same package in the same place regardless of whether I select SSIS Package Store or SQL Server. Any idea what the differences here are? They seem about the same to me.