March 25, 2013 at 4:05 am
Hi friends,
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.
Kind Regards,
March 25, 2013 at 4:23 am
Generate Config files for Dev, Test and Prod environment. [ Values to SSIS package variables like File Paths,Server Name , DB name connections,etc..]
SSIS package is same across all environments. which will take values from the config files for corresponding environment
Prepare a Deployment Guidelines for SSIS package. Below one gives info.
[http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/]
Regards,
SP
March 25, 2013 at 6:18 am
March 26, 2013 at 3:17 am
Hi Sasidhar,
Just another question: It is possible to deploy the SSIS packages in different folders in the msdb.
I create a specific structure of sub folders in the msdb to organize the packages, but when I build the solution and run the deployment utility I can only select one destination folder for the packages.
Kind Regards,
March 26, 2013 at 1:08 pm
You could look into the SQLPSX PowerShell modules, there is a module specifically for manipulating SSIS packages. We are looking into automating test deployments using this module, working nicely so far.
March 27, 2013 at 3:48 am
Hi Trond,
thanks for your suggestion. I installed the extension modules for PowerShell and they seem to be a great option not only for SSIS package management.
There are always disadvantages, like administration issues. I had to enable the execution of PowerShell scripts in my computer, later on I have to discuss this kind of issues with the admin guys. However, there are a lot of advantages like the flexibility to manage the packages (create/delete folders, importing/exporting/removing packages from MSDB, etc.)
To complement this thread I leave the link to an article of one of the SQLPSX project participants:
Importing and Exporting SSIS Packages Using PowerShell[/url]
Kind Regards,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply