Re: Migrating DTS Packages across environments

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 07/08/04 

Date: Thu, 8 Jul 2004 14:39:07 +0100

Your requirements then are slightly more complicated but not impossible.
You will have to have the package read the owner for the respective
environment from somewhere if you want this to be dynamic and not have to
open up each package. Also remember that DTS is client side so the client
will have to be able to tell which environment it is supposed to be
executing otherwise how does the package know?.

You could provide a front end for the user that fires the package and they
then choose the correct environment.

That said you could modify the migration routine to provide the migrator
with a Env --> Env selector. This could then open the package and set
values for the correct environment (Maybe just a single Global Variable).
You would build the SQL Statements then like this article.

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)

Does that make sense? 

-- 
----------------------------
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hari" <Hari@discussions.microsoft.com> wrote in message
news:E9145726-AC36-4EB6-98B7-33622CD12DEA@microsoft.com...
> Hi ,
>
> Please help me with this. I want to migrate DTS packages from one
environment to another. I have the transformdts code from www.sqldts.com
which helps in copying dts packages from one server to another. I have
modified it and now it changes the connection information (server, userid,
password, database) also. But i have a problem. I use the transform data
task. In the source query i am typing a query. But the same table has
different database owners in different environment. Now i want to change the
query also like if the database owner for server A is abc and for server B
is xyz and if the query is "select * from abc.EmpDetails". Now i want some
means to change the query also to "select * from xyz.EmpDetails". How to do
that while migrating. Please help me with this.

Relevant Pages

  • Re: DTS creating Excessive Sized Text Files
    ... The whitespace is in between the fields in the text file, ... If I run the the same query in Query Analyzer with the output to a ... I have dropped and created a new DTS package, ... > When you change the destination medium to say SQL Server what happens? ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Environment Control
    ... I know I have machines which will execute packages in a given environment. ... table on the aliased server and retrieve it through a DP task. ... In your 200 table wizard generated package you could simply use the object ... challenge is when you promote your packages up; the connections have to be ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Environment Control
    ... In your Dev environment you have a source of Dev1 and a destination of Dev2. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... >> 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. ... >> challenge is when you promote your packages up; the connections have to be>> changed to reflect the new environment. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Environment Control
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... When run from the production environment, the production server is accessed and when run from the test environment, the test server is accessed. ... 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. ... 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. ...
    (microsoft.public.sqlserver.dts)
  • Re: Change DTS Query and getting SQL Server doesnt exist or acces
    ... I am connecting directly to the server with Enterprise manager (using ... double click on the transform data task arrow, and click on the "Build Query" ... I'm trying to update a query of the transform data task of a DTS ... >> package(I've never worked with this DTS package before). ...
    (microsoft.public.sqlserver.dts)