April 1, 2008 at 9:21 am
Just throwing this out here to see if anyone else is in a similar situation and have found something that works......or if someone can just suggest some possibilities that I can look into.
What we have is a 2-node SQL Cluster that currently houses 4 instances. Dev, Test, QA and Prod. We've been using SSIS Packages to do some of nightly processing but have never really created a means for deploying packages through the environments. Basically, right now, I just develop the Packages on my local machine and then we upload them to the Production server where the Server Admin has to modify my connections to make them point to the Production database.
What we're looking for is a way to migrate SSIS Packages from the Development instance through the Production.
I have read into using XML Config files to store the connection information but since we're on a cluster we don't really have separate folders where we can store the Packages and their corresponding XML files.
Does anyone have any suggestions on the best way to approach accomplishing this?
Thanks for any help you can give.
April 3, 2008 at 7:07 am
Can anyone even just supply some possible concepts for me to research?
Totally at a dead end with this at the moment.
April 8, 2008 at 7:41 am
Why don't you use SQL Server as the config repository with a separate db for each env, each db sitting on each instance.
Paul R Williams.
April 8, 2008 at 8:28 am
Looks Like we are in the same situtaion. Rt. Now i am in a project where i am migrating almost 70 dts packages into SSIS. Most of the work for migration is done. I am confused with the way thet are telling me to make package dynamic.
i am working in QA1 server, where all the work are done.
all teh database are hosted in this server and all execuatbles files and source data for bulk insert and others are on the same server. Testing will be done on this server too.
Now when we move to production. packages will be hosted in ETL server, whereas database will be on PROD server.
and According to them all those source data for my bulk insert like txt file excel file(and may be .exe. .bat ane etc..) will be on PROD server. but when i look there .INI file in dts its just point to G drive ( and i think thats local G drive).
My question "when my packages are in ETL server, How can i point to C or D drive of PROD server Dynamically?
I can do that by Unc path (\\server\D$\............)
is there any better approach? and can someone post or suggest some ways of doing these?
What ar ethe things we should do or learn during the deployment..
KUMAR,
********
" A PROBLEM AIN'T A PROBLEM, IF IT CAN BE SOLVED".
April 8, 2008 at 8:45 am
Paul Williams (4/8/2008)
Why don't you use SQL Server as the config repository with a separate db for each env, each db sitting on each instance.
This seemed like the best bet to me, as well but I had a hard time finding information on how to implement.
Do you have any suggested reading (online or book) that covers this in great detail?
Thanks.
April 8, 2008 at 8:55 am
As normal I picked this up from a variety of sources : BOL, Internet (Database journal, sqlis.com, SSIS MSDN Forums etc. they are all a good source of info). The book I did look at also was SQL Server 2005 by Wrox.
Then it was by trial and error and there were a few finicky things to overcome.
We use SQL Server as the host repository and also an environment variable that is dynamic for the name of the SQL Server host repository itself. That way all I need to do is point to the appropriate env variable in my package before I deploy to any environment.
Paul R Williams.
April 8, 2008 at 9:34 am
Thanks Paul, App..... Ur Time !!!!
I do belive books by wrox are very helpful. I do have both expert and pro.....
As I feel little uncomfortable with variables and also because my dts don't have many variables, I decied to go with XML config file. Much easier to manage and create.
My Sceniro:-----------
My dts packages are simple and they don't use much logic. they are build for nightly processing of data for warehousing. I have completed most of the packages, except few that uses heavt active X and variables.
---------------
Now what other things do we need to know...( i am thing of building deployment file )
If u have any real hand experince, what kind of event handlers do u use ? and ar ethey for all exetuables or packages. In my case i am think to use send mail task onError as event handlers. In my other projects they have table which takes serverals variables to record the packegaes in case of failure or error.
What u think? What are the best approaches to do these things? Do u think using VSS is good ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply