Re: DTS Environment Control
From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/26/04
- Next message: TC: "Re: saving SELECT results to excel -NEWBIE"
- Previous message: Wael Fadel: "Help - Executing a DTS packages using SP_OA"
- In reply to: Joe Horton: "Re: DTS Environment Control"
- Next in thread: DHatheway: "Re: DTS Environment Control"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 26 Jul 2004 20:56:46 +0100
No .
In the pakcgaes the connectionnames for the server stay the same. It is the
aliases through Cliconfg that change in the environments.
-- -- Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. www.konesans.com - Consultancy from the people who know "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message news:%23zCPmgzcEHA.2352@TK2MSFTNGP09.phx.gbl... > Doesn't this approach still mean that I have to change all my connections as > they move up the environments? > > In my example below, as I promote my packaged from DEV to TEST - If I'm > understanding your approach, I would still need to change the Connections > for step #1 and the UNC path connections for step #3. > > Dev Environment: > 1) Connection Source to SQL DEV Box > 2) Transform Task to a UNC file share on the dev box - transforms 1 table to > text > 3) Text Destination UNC path > > Test Environment: > 1) Connection Source to SQL TEST Box > 2) Transform Task to a UNC file share on the test box - transforms 1 table > to text > 3) Text Destination UNC path > > Now if we used the aliasing sort of like a DSN connection - I could simply > call my connection SQLSource in all my packages - but the SQLSource Alias on > Dev would point to dev, on test SQLSource would point to my Ttest > environment and so on...I guess I'm missing why we would use a source called > Dev1, Test1, etc? > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > news:u7S35FIcEHA.3824@TK2MSFTNGP10.phx.gbl... > > In your Dev environment you have a source of Dev1 and a destination of > Dev2. > > In your Test environment they are called Test1 and Test2. etc etc > > > > You could use Alias names of Source and Destination > > > > On the PCs that will execute the packages in each environment you set up > the > > aliases to point to the correct server. This means they resolve at > runtime > > to the correct servers. At design time you create the aliases on your box > > and use them in the Connection properties. You never need to change them > in > > the package. > > > > > > > > -- > > -- > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > www.SQLDTS.com - The site for all your DTS needs. > > www.konesans.com - Consultancy from the people who know > > > > > > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message > > news:eaX80fCcEHA.384@TK2MSFTNGP10.phx.gbl... > > > So what sort of alias names do you use - I guess I'm sort of confused > how > > to > > > use it. > > > > > > Let's say my servers just happened to be named by environment, so I > would > > > have SQL boxes called: DEV, TEST, PREPROD, and PROD. > > > > > > How would I set up my aliases? > > > > > > > > > "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message > > > news:ucxN8JAcEHA.4092@TK2MSFTNGP10.phx.gbl... > > > > You are right it can be hairy. > > > > > > > > I have the same environments as you so I do this. > > > > > > > > I know I have machines which will execute packages in a given > > environment. > > > > I am about the only person that will execute packages in all 4. I > like > > to > > > > make aliases to my SQL servers using CliConfg. This way I do not need > > to > > > > ever change them again after I have built my packages. If i need > other > > > > information like The XL file for production location I can store that > in > > a > > > > table on the aliased server and retrieve it through a DP task. > > > > > > > > This means I need to make sure that the boxes that will call my > packages > > > in > > > > each environment have aliases set up. The users will not have > > permissions > > > > onto environments which they shouldn't. > > > > > > > > In your 200 table wizard generated package you could simply use the > > object > > > > model to change the Data Source properties to an Aliased Server then > > save > > > > the package back out. > > > > > > > > HTH > > > > > > > > -- > > > > -- > > > > > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) > > > > www.SQLDTS.com - The site for all your DTS needs. > > > > www.konesans.com - Consultancy from the people who know > > > > > > > > > > > > "Joe Horton" <horj235 at lni dot wa dot gov> wrote in message > > > > news:uqvJkDAcEHA.4092@TK2MSFTNGP11.phx.gbl... > > > > DTS Environment Control > > > > > > > > > > > > > > > > What solutions have other worked up to handle DTS packages and > > promotions > > > > through their different environments? > > > > > > > > > > > > > > > > In our scenario we have a 4 tier environment (Dev/Test/PreProd/Prod). > > The > > > > challenge is when you promote your packages up; the connections have > to > > be > > > > changed to reflect the new environment. For simple packages it's not > > too > > > > big of deal - but for more complete ones - you can spend an hour > > carefully > > > > changing all your connections. Try promoting a package that > > > imports/exports > > > > 200 tables that was created with the wizard - there is a separate > > > connection > > > > for every table. > > > > > > > > > > > > > > > > I had one solution where we would add an ActiveX script task as the > very > > > > first step, that would determine what SERVER the package was executing > > > > from - and based on that would dynamically change all the connections > by > > > > looking up the package name and server from a DB. Of course that was > > > hairy > > > > to use because when you develop your DTS package and run it, it is > > running > > > > in the context of YOUR machine, despite being saved on a different > > server. > > > > The environment control solution I developed was really only useful > when > > > the > > > > package was ran as a scheduled job - as then it was truly running as a > > job > > > > on one of the 4 tier environments. The DBA's shot my solution down > as > > it > > > > required that I keep the batch login user names and passwords in my > > > control > > > > table that was used to change the connections based on the environment > > the > > > > package was running from. > > > > > > > > > > > > > > > > The other problem is source control - we use VSS. If I have a package > > > > called XYZ that grabs files from an FTP location every morning - my > XYZ > > > > package for DEV will look different than the XYZ package for TEST. I > > > could > > > > pre-pend the environment in front of the package name and have > DEV-XYZ, > > > > TEST-XYZ, PREPROD-XYZ, PROD-XYZ - but then imagine that we decided to > > add > > > > one more step to message the data further or pick up more files within > > the > > > > same package - I would have to make the changes to 4 different version > > of > > > > the package called XYZ - or make it to one and re-save it across the > > > > environments and change the connections for each environment. > > > > > > > > > > > > > > > > Anyone have better solutions? > > > > > > > > > > > > > > > > > > > >
- Next message: TC: "Re: saving SELECT results to excel -NEWBIE"
- Previous message: Wael Fadel: "Help - Executing a DTS packages using SP_OA"
- In reply to: Joe Horton: "Re: DTS Environment Control"
- Next in thread: DHatheway: "Re: DTS Environment Control"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
