7

I have a server with a working installation of the Sql Server 2012 SSIS Catalog. I need to set up an additional instance including the SSIS Package Store service as an interim step while the packages are being re-written. The Package Store is a per-server feature, not a per-instance feature. Can these two features operate side-by-side?

1 Answer 1

7

There are two choices where to store SSIS Packages: the file system or the database. Within each of those, there are two variants.

The File System supports anywhere you want to put packages. The Package Store is just a well known location of %Program Files%\Microsoft SQL Server\NNN\DTS where NNN matches your version. The challenge with the Package Store is that for multi-instanced servers, everything is mixed together. SSMS, I would specify I want to connect to Server\DEV or Server\TEST. The Integration Services connector though, I'm just connecting to Server. You can create subfolders to attempt to isolate packages for environments but it's clunky. Not as clunky as the other approach of editing/swapping in different XML files to redirect where the Integration Services process defines the Package Store...

The Database only option pre-2012 was going to be tables in the msdb (and all the dba's rejoiced at the added backup bloat and testing of restores for msdb too). Depending on 2005 vs 2008 will the table will be called dbo.sysdtspackages90 or dbo.sysssispackage and they may also live in folders. Handy SSIS Package query

2012+ forward, we have a new choice and that is the SSISDB. There's a one-time creation required but it is per instance so you get proper isolation. This new SSIS repository though is only for packages that have been built under the Project Deployment Model. Before this, packages were a cohesive solution in Visual Studio/BIDS/SSDT but that was only for the development experience. After that, the packages could be spread to the four corners of the globe which is unfortunate if one package depended on another being in the same folder. The Project Deployment Model attempts to address this shortcoming by "compiling" all the packages in an SSIS project into a .ispac file. By compiling, it's really just zipping them together with a manifest file but now we have a deployable quantum and that's all you need for this to successfully run on a new environment *.

* As long as you have already installed the drivers, third party applications, set file system permissions, created users, applied configurations, etc but it is an improvement.

Back to the original question

Can the Package Store, a server wide feature, coexist with the new instance scoped SSISDB? Sure, why not?

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.