SSIS Package Store vs. MSDB

8
votes

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?

1
sql-serverssis

1 Answers

11
votes

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

enter image description here

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