I've a task to make dynamic connections to transfer data from Sybase Server
to SQL Server by DTS. The servers, databases and tables are dynamically used
in INI file by dynamic properties task.

For example, tables (T1,T2,T3) are used by each database(DB1,DB2,DB3,DB4)
and each server(S1,S2).

The Sybase architecture is as follow:
Source Servers: S1 S2
Source Databases: DB1, DB2 DB3, DB4
Source Tables: T1 T2 T3,T1 T2 T3 T1 T2 T3, T1 T2 T3

Currently, I've to build a package for 12 connections to complete the above
task to transfer data from Sybase Server to SQL Server.
For example, ie. 1. S1->DB1->T1 2. S1->DB1->T2 3. S1->DB1->T3 4.
S1->DB2->T1....etc

How to transfer data dynamically in a package to improve it?

What you can do is build a rowset of values
You loop through those values and set the properties of your tasks.

How to loop through a global variable Rowset
(http://www.sqldts.com/Default.aspx?298)

Your package then loops around until it reaches the end of the rowset.


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"sbox" <s@b.x> wrote

Thanks. I could follow the example to make a loop to call values retrieved from a table. But I encouter an error if I use more than one rowset of values. The values retreived are incorrect by repeat the looping and the error says "Either BOF or EOF is Ture, or the current record has been deleted. Requested operation requires a current record.

Are there any example to loop through multiple global variable rowsets



----- Allan Mitchell wrote: ----

What you can do is build a rowset of value
You loop through those values and set the properties of your tasks

How to loop through a global variable Rowse
(http://www.sqldts.com/Default.aspx?298

Your package then loops around until it reaches the end of the rowset


--

---------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP
www.allisonmitchell.com - Expert SQL Server Consultancy
www.SQLDTS.com - The site for all your DTS needs
I support PASS - the definitive, global communit
for SQL Server professionals - http://www.sqlpass.or


"sbox" <s@b.x> wrote in messag
news:%23szMdIm6DHA.3288 (AT) TK2MSFTNGP11 (DOT) phx.gbl..

So your SELECT/Proc that populates the GV returns multiple rowsets to the
caller?

Never tried it and don't know of any examples.

You could try building the Rowsets through ADO in an Active Script task so
you can enumerate them. In each enumeration you call the second package
which is the one in which you set the values




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"sbox" <anonymous (AT) discussions (DOT) microsoft.com> wrote

values. The values retreived are incorrect by repeat the looping and the
error says "Either BOF or EOF is Ture, or the current record has been
deleted. Requested operation requires a current record."

I've tried another solution. I've set the global variables for source and destination servers, databases and tables in Dynamic Properties Task Properties.
For testing, I just hardcode the global variables and changed them manually after each package execution.
Whenever the global variables change, the transformations need to be manually re-mapped. How to make it effeciently to do the auto-mapping in dynamic transformations ?

Also, how to build a loop to store the global variables in ActiveX script task? It seems difficult to trace the loop if there are multiple variables in both workflow script and task script. Is there any proper solution to do a loop for dynamic data transformations?

As I said in my previous post, if the source AND/OR destination definition
changes there is no switch to do "Auto remapping". This has to be done
manually.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"sbox" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Properties.
dynamic transformations ?
in both workflow script and task script. Is there any proper solution to do
a loop for dynamic data transformations?

As you've said in the previous post, the transformations will go out the window and involve getting down wit
the object model. Could you talk about it in details? Also, I could not email you by allan (AT) no-spam (DOT) sqldts.com to ask for your package

Thanks if you could reply me.

As you've said the auto remapping invlove getting down the object model. Could you talk about it in details?
Could your package do the auto remapping?

I could not email you by allan (AT) no-spam (DOT) sqldts.com to ask for your package. Thanks if you could reply me.

remove no-spam

You are basically looking at the Transformation Object and its SourceColumns
and DestinationColumns properties.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"sbox" <anonymous (AT) discussions (DOT) microsoft.com> wrote