sql server - SSIS Package Store vs. MSDB -

While setting up a SQL Agent job, among others, to choose "SQL Server" or "SSIS Package Store" Option. "If I select" SSIS Package Store "and browse MSDB instead of file system, then I can choose the SSIS package stored in sysssispackages.

However, / DTS Documents for switches, which selects SSMS itself when selecting SSIMS package store It appears to be the only package stored on the file system.

I also saw that if the user executing the task does not have file system rights, then the SSIS package store -> MSDB -> When selecting the package, "Access Denied" error or "object does not exist". Since the package is not present on the file system, execution executes while selecting SQL Server and uses the SSIS package store Fails while doing? Is DTXA really looking for a DTSX file on the file system, whenever MSDB is chosen? I have probably got wrong, understand that SQL Server and SSIS package store -> MSDB is the same That is, the matter on which the agent is running refers to that server.

I have done some research and I can

do some light on any difference?

Div>

There are 2 locations for storing the SSIS packages: the file system or the SQL Server balance is semantically rescued.

File System

You can use the SSIS package store, which is a famous location in the installation location

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

or you can choose anywhere on the file system of your choice. If you go this route, you will need to ensure the SQL agent account or the credential proxy, or if you are running the package from XPICDSL, the SQL Server service account has access to that location.

The only benefit, if you want to call it, is to use the package store (i mean the folder I mentioned), you can use the integration services management tool that is present in SSMS (Instead of the database engine by connecting to the integration services)

Although there are so many disadvantages such that many instances are not capable of handling, the package only runs in 64 bit mode Go, proxy b TE does not reach, and so on.

SQL Server

If memory works correctly

• 2005 - stored in msdb.dbo.sysdtspackages90
• 2008 - Stored in msdb.dbo.sysssispackages (I used to reuse the RTM, or 90 table by using a separate table in 2008, but it seems to be missing out on compromised)
• 2008 R2 - msdb Stored in dbo .sysssispackages
• 2012 (package deployment model) - stored in msdb.dbo.sysssispackages
• 2012 (project deployment model) - SSISDB.catalog.packages stored in ^*
• 2014 (package deployed) Models) - stored in msdb.dbo.sysssispackages
• 2014 (project deployment model) - stored in SSISDB.catalog.packages ^*

^* With the project deployment model, the packages are "compiled" (zipped from a manifest) .ispac in which SSISDB.internals * The tables are stored in the intestine. * Table.

Wrapup

Eventually, where you store your packages does not affect your ability to run. You can use DTEEXEC, SQL Agent or Custom. Using the net code, you can run the package. The choice of the storage package depends primarily on your management style.

Context