Dear All, I asked this question some days ago but still
unable to resolve the issue. Can anybody kindly guide me
as I am new to DTS environment in SQL Server 2000.

I am building an application in Visual Basic 6.0 with SQL
Server 2000. It will move data (mainly account numbers and
associated information like, customer, cost, etc.) between
different servers whenever a batch is paid-off. A batch
consists of different invoices & each invoice is for a
particular account number.

What I am trying to accomplish is to pass a global
variable (which will have batch number information) from
VB6 application to DTS package which will create a SQL
recordset linking account, customer, cost, batch, etc.
tables for that particular batch number (this recorset can
have number of rows anywhere from 1 to 200 depending on
size of batch and number of invoices). VB6 application
will execute this DTS package and transfer recordset data
between different servers. HOW CAN I DO THAT ?

Your help will be highly appreciated. Cheers.

Seeing as you are executing this in VB I would be tempted to

1. Set your DataSource properties of the Connections objects through the
object model
2. You can easily use parameters to specify the account number with which
you want to work in the datapump task itself.

SELECT ............. FROM.......... WHERE <col> = ?

You map a global variable to the ?
In your VB code you specify the value of the Global Variable

3. You say you build a recordest built from n tables in the source. To how
many tables in the destination will that be delivered?
You of course know that without using Lookups that you can only insert into
1 table at a time. Lookups perform their actions on a Row*Row basis so can
be slow.
Therefore i think your solution is to have n DataPump tasks selecting the
correct data from each source table and inserting it in the correct order
into your destination tables. You ensure they are moved in the correct
order by using Workflow in your package.

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)


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


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