Hi All,

There are 20 Databases on my server. Each one has same structure, but for
dfferent companies.
I have a DTS which transfers data from Source server [erp] to another server
and then the same data to text files. Presently it's on development and will
be shifted to live server.
It's obvious that, I have to create different DTS for connecting to those
databases ,
Now, My DTS is same for all of them, excluding the Source and destination
databases and text file names. I am storing them in INI file and using it.

There is a SQL task which has a query like
"Select field1....., field10 from [servername].[dbname].dbo.tablename where
..."
here [servername] is not the local server name.
Except for "[dbname]", everything is common in all dts. I want this to come
from INI and yes also the Server name.
Is it possible? how?
note: presently i am using storeprocedure for this task, and for each dts
there is a SP.

thanks and regards,

You are going to have to build this statement synamically but don't worry
it's not all that hard

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


"k_s" <ks (AT) discussions (DOT) microsoft.com> wrote

Look at Dynamic Properties task. You can change the properties of just
about everything in a DTS based on code, variables, constants,
whatever. Use a global variables.

Now create a 2nd DTS that will execute the first DTS X amount of times.
X being the number of variants you need. If you examine the properties
of the execute package task, you can see that you can pass variables
into and out of the package.

Then just make sure you have the ODBC's setup in the main DTS and you
should be good to go that could save you some time if the only
difference is the source/destination server/database. If structure
varies, then you really just need to code it.

I'm not proficient enough to know for sure, but I think you can do it
in a stored procedure by passing variables, but that should require
linked servers on the server that will be executing the code.

Thanks Mnemonic.
Point taken Allen.

Let me put the situation clear.

I have an Application Server AS, and a OLAP Server say OS.
AS has diff databases for each company, there are total 10 databases. This
is done to keep every companies data seperate, though the structure is same
for all of them.
The data comes from AS to OS , where staging database and cubes are created.
We r using MS SQL Analysis services.

Everything's working fine, and now we have to preapare a back-up plan. There
are two backup servers for OLAP and OLTP. So when any of the live server has
some problem, the respective server will be up. Suppose, if OLTP server is
down then AS_PCR will be up and if olap server goes down then OS_PCR will be
on.

We want our DTS to take care of the same. So source and destination servers
should change.
I am working on a test dts. I have used ini file, which has servername etc
and in DTS I am using dynamic properties. Everything works fine except for
one task which is an SQL TASK and has a delete statement in it.
It is actually deleting data from OS by comparing it with AS.
something like ..

Delete [Tablename] Where TimeStamp not in
[Select TimeStamp from [AS.DatabaseName.dbo.TableName]

Here source database is in the sql statement. I need this to be dynamic.
Even if i create procedure, i need to change the server name or pass it as an
argument. It should be from INI file.

I hope I have made the point clear this time atleast.

regards
kshitij.





"Mnemonic" wrote: