HI, we are beginning a new project at my company and I was wondering where is the best place to save SSIS packages: file system or SqlServer. I have used other ETL products and they always create a repository on an RDBMS. Since SSIS offers us the choice of DB storage or file system, is there pros and cons of both approach Will the deployment of our application be simpler by using Sql Server since we would onky move metadata instead of files
Thank you for your help,
Ccote
Storage of packages: file system or Sql Server?
jbmac
ahmed_black_horse
Hi Jamie, thanks for your fast answer. I was just wondering for migration purpose (moving project onto different computers, sharing project) if it would be simpler to use Sql Server as repository of all packages. Other ETL tools (Hummingbird ETL, Informatica for example) use a DB repository to store their packages equivalent and when we want to migrate this repository onto another computer, it is simply an import/export of the repository and everything in bundled into this file.
If our packages would be stores in SQL server, would we be able to simply detach or backup and attache or restore the db. Or with multiple projects, export/import packages Aslo, it is worth to mention that we use VSS, are there cons of using Sql Server storage for what I want to do
Thank's again,
Ccote
Adam Baruh
This might be worth reading for you: http://blogs.conchango.com/jamiethomson/archive/2006/02/20/2902.aspx
Make sure you click through to Kirk's blog (link provided therein) to see his take on this as well.
-Jamie
Sarath C
Detaching and reattaching msdb databse (which is where the packages are stored is not recommended. Informatica's (and Hummingbird's I assume) architecture is different in that they rely on a metadata repository which facilitates this sort of migration.
It is still possible with SSIS though. You should use the deployment wizard in order to deploy your packages. When you run the resultant .exe you are prompted which server to deploy the packages to. In your case you just run the same .exe and deploy it to the new server.
-Jamie