SSIS for Multiple Environments

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Zach Mattson » SSIS for Multiple Environments

20 posts, Page 1 of 2

1 2 »»

SSIS for Multiple Environments

Rate Topic

Display Mode

Topic Options

Author

Message

WI-DBA

Posted Monday, April 26, 2010 9:31 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:36 AM
Points: 93, Visits: 545

Comments posted to this topic are about the item SSIS for Multiple Environments

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Post #910803

danv

Posted Monday, April 26, 2010 9:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:33 PM
Points: 163, Visits: 121

This topic definitely needs more attention. It wasn't/isn't a pleasant process. So great job on laying some ground rules.

Being able to promote smoothly through environments (4 in our case) was high on the list on our newest project. I can't say it was pretty, in the end we went with XML config files with relative paths. What's good is that it works, and we never mess with anything but the configurations once it starts moving through the promotion process.

I can really see the attraction of database configurations but I cannot ever get comfortable with environment variables... maybe it's just me.

What I have yet to see is a good (hack or not) way of securely maintaining credentials etc for say connection managers which don't authenticate with Active Directory... e.g Oracle.

Post #910805

djenkins

Posted Monday, April 26, 2010 9:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 28, 2010 10:59 PM
Points: 2, Visits: 11

Interesting article. I have actually implemented something similar in my environment with SSIS 2005.

In my case, my enterprise job scheduler executes my NT Batch files which use the DTSRUN command to call my SSIS packages. Instead of using a configuration database like the article explained, I used exclusively the DTSConfig file.

Another way to call the DTSConfig regardless of the environment is by referencing it by an environment variable. That variable can be created directly in your batch file and will remain in-scope during your execution of the SSIS package. This is a clean way to have your batch scripts accept a parameter (like your PROD, QA, or DEV DTSConfig file), Pass it to the Environment Variable, and have it directly change connection strings, SSIS variables, etc.

Please contact me if you want an example.

Post #910808

oliver.cox

Posted Tuesday, April 27, 2010 2:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 01, 2011 3:16 AM
Points: 77, Visits: 10

Hi,

You say if I get promoted claim the idea as my own but what do I do if it got me fired?

I'm joking of course, I like the approach a lot.

Thanks

Olly

Post #910885

Koen Verbeeck

Posted Tuesday, April 27, 2010 3:00 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 9,372, Visits: 6,469

Something that is not very clear for me on a first read:
do you have one configuration table for all the environments or has each environment its own config table?

If it is the latter one, why use a .dtsconfig file?
In my set-up (each environment has its own config table), the environment variable points directly to the config table.

Another question: why don't you store the packages in the MSDB folder? Do you have specific reasons to do that?

And to end my reply: I think a nice addition to the article is an explanation on how to configure your packages using the configuration table. E.g. using the package configuration wizard in BIDS (of which you have 1 screenshot).

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #910898

ryan.lawrence

Posted Tuesday, April 27, 2010 5:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 03, 2012 10:08 AM
Points: 214, Visits: 81

we have found that DTSConfig files work great for storing the parameters for the database, as you don't very often move your application from one database server to another except during upgrades and lease refreshes, so that file remains fairly static by environment.

we use SSIS Configurations table in SQL to store all other "variable" data such as flat file paths and that gives us the ability to manage the values programmatically or via SSIS to move changes up into test and prod from dev. plus it's very easy to write an update query to change your path with a few well placed charindex and substring or replace statements

we also call all our jobs via a production scheduler using dtexec passing the config file into the command line.

This also allows you to only need control over dev and test, and when you we need something promoted to production you can just ask the DBA team to copy the dtsx file from test to prod, and leave the dtsconfig file alone.

Post #910985

WI-DBA

Posted Tuesday, April 27, 2010 6:20 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:36 AM
Points: 93, Visits: 545

"do you have one configuration table for all the environments or has each environment its own config table?"
We maintain one configuration table per ETL server (because each physical machine can have only one environment variable - that has the config information in it)

If it is the latter one, why use a .dtsconfig file?
That portion is mostly useful for developers to easily repoint their configurations to a different table or server for testing if need be.

Another question: why don't you store the packages in the MSDB folder? Do you have specific reasons to do that? My preference is the file system - and it just lays out nicely for the process we use.
This article probably moved me to this method - http://bi-polar23.blogspot.com/2008/06/ssis-and-sql-server-agent-choosing.html

And to end my reply: I think a nice addition to the article is an explanation on how to configure your packages using the configuration table. E.g. using the package configuration wizard in BIDS (of which you have 1 screenshot).
(Noted - my thinking was this was covered pretty well elsewhere, but maybe not)

Cheers
http://twitter.com/widba
http://widba.blogspot.com/

Post #911020

Koen Verbeeck

Posted Tuesday, April 27, 2010 6:29 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 9,372, Visits: 6,469

Allright. Thank for the reply and the link!

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #911029

Daniel Bowlin

Posted Tuesday, April 27, 2010 7:22 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 2,672, Visits: 2,417

This is a really interesting article that demonstrates the power of thinking ahead and planning before jumping into the work. I expect I will read this article a few times to make sure I get it completely, and then I expect I will set up something quite similar in my environment.

Thanks.

Post #911087

danv

Posted Tuesday, April 27, 2010 9:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:33 PM
Points: 163, Visits: 121

The approach of setting an environment variable in the scope of the batch I do like. Our relative path method for configs and logs works great for a one time file copy/ftp to a folder, no other intervention necessary except setting up the job.

What I also find interesting is how many people are using DTSRun in a CmdExec. Like WI-DBA the same bi-polar article helped in making the case. One issue I have with DTSRun is that there is no way send it a kill signal remotely - anyone have any experience with that?

Post #911214

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions