SQLServerCentral / Discuss Content Posted by Zach Mattson / Article Discussions / Article Discussions by Author / SSIS for Multiple Environments / Latest Posts

RE: SSIS for Multiple Environments

danv — Wed, 28 Apr 2010 21:50:34 GMT

ryan thanks alot for the info.robert. are you referring to the package protection level?when we are in development we EncryptSensitiveWithUserKey enabling each developer to do their own thing while working locally. when we put our packages to DEV or UAT or PROD environments we EncryptSensitiveWithPassword with a strong common password. this might have some useful info: http://msdn.microsoft.com/en-us/library/ms141747.aspx

RE: SSIS for Multiple Environments

SQLRNNR — Wed, 28 Apr 2010 10:43:18 GMT

Nice article and very useful information.We use a table in each environment.

RE: SSIS for Multiple Environments

Toby White — Wed, 28 Apr 2010 10:17:30 GMT

[quote]One question I have is when developing the package on your local machine and then running tests in each environment do you set package security up to a specific account?[/quote]The way I handle this is by using the SQL Agent account or proxy. The package doesn't have any different security persay, but the security of the different agent/proxy accounts varries. Infact I developed a way to have one SSIS_configurations table for several environments.SSIS_configurations can be a view/function that returns the data depending on what account (suser_sname()) calls it.

RE: SSIS for Multiple Environments

WI-DBA — Wed, 28 Apr 2010 10:15:37 GMT

When a developer runs the package from their machine, it will (in our case) connect as dbo to the Configuration View - the reason being the SSIS developers our part of a group that has higher privs on DEV. If you use sql logins, there will be a bit more management involved when doing the promotes from the developer, to the qa system, etc. When we move the package to a Test environment, the credential/proxy take care of the security. The package execution takes on its (job step owners) credentials. If I missed the question, please re-phrase and I will give it another shot.

RE: SSIS for Multiple Environments

Robbob — Wed, 28 Apr 2010 10:02:07 GMT

Hi this is a great article. I am very familar with SSIS and would like any information on where I can get some more specifics on Multi Environment implementation. I have not had the need to create a Proxy or a credential account as you mention in your article. so I am trying to follow along and I think I have the jist of it. One question I have is when developing the package on your local machine and then running tests in each environment do you set package security up to a specific account? We have had some issues here with the credentials being saved as part of the package so we just use Passwords but I would like to know more about the credential implementation. Any info anyone can give on this topic is greatly appreciated. Thanks,Rob

RE: SSIS for Multiple Environments

ryan.lawrence — Tue, 27 Apr 2010 11:56:32 GMT

zarathustra >>> we use CA Autosys

RE: SSIS for Multiple Environments

danv — Tue, 27 Apr 2010 11:19:26 GMT

<ryan>Interesting, that's how we'd like to ours to behave.We use SQL Agent as our job scheduler. Stopping the job step leaves a thread of dtexec running the package and it won't stop until killed by pid. What job scheduler do you use?</ryan>As for storing packages in the filesystem: 1) it eases our deployments 2) you can't use relative paths in MSDB. Security needs to be taken care of with folder permissions etc. One disadvantage to this is also that we're not having the packages backed up as part of the msdb backups, but rely on our storage backups in the data center - which we are ok with.

RE: SSIS for Multiple Environments

djenkins — Tue, 27 Apr 2010 10:01:58 GMT

Actually I reviewed by NT script and I do use DTEXEC instead of DTSRUN (I must had DTS packages on my mind!). Another great thing about using a NT Batch script is that you can evaluate the ERRORLEVEL code from the DTEXEC command. This is a great way to properly know if you package did succeed or not. This also allows you to properly pass the Exit code to your job scheduler (ie: IBM Tivoli Job Scheduler) to know if the job was successful.You can findout all about the DTEXEC command in BOL.

RE: SSIS for Multiple Environments

Jorge Novo-@ETLDEVDBA — Tue, 27 Apr 2010 09:46:29 GMT

Why storing the ssis at the File system level instead of the server? Storing packages at the file system is a security problem. A better way will be to store the package in the server also, store all metadata relate information in a table, this will provided a better more stable package to maintain.

RE: SSIS for Multiple Environments

ryan.lawrence — Tue, 27 Apr 2010 09:30:31 GMT

we use dtexec, which when run in the context of a scheduler will respond to a kill statement.if we kill the job in the scheduler it will kill the ssis package at the point it was processing in about 3 - 4 seconds.

RE: SSIS for Multiple Environments

danv — Tue, 27 Apr 2010 09:03:41 GMT

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?

RE: SSIS for Multiple Environments

Daniel Bowlin — Tue, 27 Apr 2010 07:22:08 GMT

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.

RE: SSIS for Multiple Environments

Koen Verbeeck — Tue, 27 Apr 2010 06:29:29 GMT

Allright. Thank for the reply and the link!

RE: SSIS for Multiple Environments

WI-DBA — Tue, 27 Apr 2010 06:20:24 GMT

"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.htmlAnd 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)

RE: SSIS for Multiple Environments

ryan.lawrence — Tue, 27 Apr 2010 05:38:13 GMT

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 statementswe 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.

RE: SSIS for Multiple Environments

Koen Verbeeck — Tue, 27 Apr 2010 03:00:20 GMT

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).

RE: SSIS for Multiple Environments

oliver.cox — Tue, 27 Apr 2010 02:26:41 GMT

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.ThanksOlly

RE: SSIS for Multiple Environments

djenkins — Mon, 26 Apr 2010 21:58:53 GMT

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.

RE: SSIS for Multiple Environments

danv — Mon, 26 Apr 2010 21:46:28 GMT

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.

SSIS for Multiple Environments

WI-DBA — Mon, 26 Apr 2010 21:31:16 GMT

Comments posted to this topic are about the item [B]SSIS for Multiple Environments[/B]