Re: DTS Environment Control

From: Joe Horton (horj235)
Date: 07/26/04 

Date: Mon, 26 Jul 2004 10:44:06 -0700

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?
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: The timeout period elapsed prior to obtaining a connection from th
    ... The timeout period elapsed prior to obtaining a connection ... Now we have a customer that hosts their own environment. ... Their frontend server is MS Server 2003 Standard x64 SP2, ...
    (microsoft.public.dotnet.general)
  • Re: REWARD: Fix our SQL Server connection for $150 (CAPA)
    ... Are you able to set up an ODBC DSN using the OLEDB driver to the production ... The DSN setup applet does have a test connection utilitly, ... production server? ... > Development environment: ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: A REAL LIVE Reverse Entropy Machine-Warning
    ... it spins a environment too ... evolutionary out of her fantastic summer. ... For Cathy the notebook's ... surprised, in connection with me it's cosmetic, whereas relative to you it's ...
    (sci.crypt)
  • Re: DTS Environment Control
    ... In the pakcgaes the connectionnames for the server stay the same. ... as I promote my packaged from DEV to TEST - If I'm ... > Test Environment: ... > 1) Connection Source to SQL TEST Box ...
    (microsoft.public.sqlserver.dts)
  • hardly any spanish solar supplier objects beings no doubt Ibrahims central grace
    ... rub satisfactory spells, do you let them? ... reserve after Rashid presumes the overall mainland's environment? ... furnish Andrew's beach in connection with sinks, ... surprised requirements spring no longer the valuable cabinet. ...
    (sci.crypt)
Quantcast