Hello SSIS Masters, I'm currently going through the learning phase. I have two questions regarding package deployments:
Q1. What is the ideal mechanism to deploye the packages (and which one is GENERALLY fllowed across organizations) :
a. By using deployment utility
b. From within BIDS or Management Studio
c. From a command line using dtutil or xcopy
Q2. Where should you ideally place the your packages (and where are these GENERALLY stored across organizations):
a. in msdb database in SQL Server instance
b. in SQL Server instance
b. in file system
The question might seem foolish but i'm new and any clarification will help.
In my opinion, this decision would be driven by kind of environment you are supporting and number of ssis package you have to support.
You can loosely say, SSIS is not instance aware.
In case you are having Huge Boxes with multiple SQL Server instances created on them, then msdb would not be a good choice as SSIS packages for all instance would be saved under one msdb.
For instance : you have 4 SQL instances being hosted on a box and you choose msdb as a storeage area for your packages. In this scenario, SSIS packages belonging to all instances would be hosted in msdb of one instance only, you can chose this instance by updating .xml file.
So , in this scenario it would be better to store ssis packages as file shares.