Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS packages deployment strategies Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
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,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1434803
Posted Monday, March 25, 2013 4:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 1,059, Visits: 693
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
Post #1434808
Posted Monday, March 25, 2013 6:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
Hi Sasidhar,

Thanks for your comment. I didn't know that this deployment utility exist. It would have saved me a lot of time.
I will try it.

Just one thing. I think the correct link to the article is: Deploying SSIS Packages in SQL Server 2005

Kind Regards,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1434858
Posted Tuesday, March 26, 2013 3:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
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,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1435340
Posted Tuesday, March 26, 2013 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:40 AM
Points: 2, Visits: 11
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.
http://sqlpsx.codeplex.com/
Post #1435662
Posted Wednesday, March 27, 2013 3:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
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

Kind Regards,


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1435810
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse