I would like to discuss some ideas toward the deployment of SSIS packages across servers (Development, test, production and so on).
Background: As a new standard in my current company, the developers have no access to the productions servers anymore. Therefore, we must deliver the packages and other script to the admins and operators, and they should deploy them in other servers. Because the know-how and SSIS knowledge of the people in charge of the deployment is limited (at least at the moment) we should make the deployment process as easy as possible.
In order to accomplish this task I proposed the following steps which should be improved:
1. Recreate the configuration database and its correspondent configuration table in the target server.
2. Export the configuration data (using the Management Studio or other method).
3. Copy the XML configuration file that holds the connection string to the configuration database.
4. Create an environment variable to store the locations of the XML configuration file.
5. Edit the connection strings and other values in the XML configuration file and in the SSIS Configuration Table.
6. Create the needed folders in the msdb database.
7. Transfer all of the SSIS packages (I create a stored procedure that uses the dtutil shell command to move all of the packages in a specific folder) to the correspondent folder in the target server.
8. Move the log files to a new location in the target server and edit the correspondent path in the configuration server.
9. Export and Import the SQL agent jobs.
For a better understanding I should explain my architecture a little bit:
To hold the configurations I’m using the standard SSIS Configuration Table. The connection string of this table is stored in an xml configuration file and finally, the location of this file is stored in an environment variable.
The packages are stored in the msdb database in different folders.
The logs files of the application are overwritten and have a fixed name.
The execution of the packages is automated and scheduled using SQL agent Jobs.
I am looking forward to reading your comments.