Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS packages deployment strategies


SSIS packages deployment strategies

Author
Message
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 654
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
Pulivarthi Sasidhar
Pulivarthi Sasidhar
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 988
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
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 654
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
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 654
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
Trond Hindenes
Trond Hindenes
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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/
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 654
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search