Hi,
I am new to SQL Server and to DTs. I have to design a DTS package for
the following situation and I am not able to decide how to design it.

I need to do source filtering based on the destination table's rows
values.

There is System A with IBM DB2 running on AS/400.
There is System B with SQLServer on Windows.
System B is for CRM purposes. and A is for accounting.

There is a Client_master_A table in dB2 ( for system A) and
a client_master_B table ( for system B ) in SQLServer database.

Client_master_A contains data for accounting clients as well
as CRM clients So it has the clients in Client_master_B as
well as other non-CRM clients.


When an existing client's details are changed, that is
when the rows of client master have to be changed, the change is
made in System A. The 2 systems have to be brought in sync
for the client master.
This is done by a Java Application which takes list
of client_ids in client_master_B, gets data from client_master_A
only for these client Ids and updates client_master_B.
Client IDs are stored in a Java Arraylist.


Since this takes place in a loop, and involves record by record
processing, we want to do this thru DTS.


The problem is getting the list of B's client IDs for making
the SELECT SQL stmnt to get data from A's table.


Also, we cannot make a new table in A where
we can store/maintain/update B's client IDs.

I want to know how to solve this problem.


I am thinking of using a lookup table while transferring rows, or using

a DDQT (Data driven Query task) and filtering the source rowset
in system A or using at last resort using a ActiveX script.


Using an ActiveX script is not very acceptable as I have
never programmed in that before and that will result in buggy code.
Also row by row processing will slow down the data transfer which is
the problem that we are trying to solve.


Apart from the fact that the two databases are separate
and on separate machines, there is also the factor that
we cannot hard code the client IDs in theSQL query,


When we tried took the SQL made by the Java application
for extracting data from A, and ran it on DB2, there was
a error of "Max size of 2048 characters reached.....Exceeding limit
of variable, literals, constants in string". The client list is quite
long and so we got this error. So running the SQL directly is also
out of question.

My PM suggests that we can take the client Id list chop it into
small "batches" and then build the SQL query and execute it
in DB2 and transfer data to SQL server.
Can anyone tell me how to do this with the Package Designer.

Can a global variable solve this problem like populate a globa
lvariable with
client IDs from Client_master_B and then loop through Client_master_A
and transfer the matching rows only into Client_master_B.

Last, if nothing works, can I transfer the client IDs to a temporary
file ( created in the DTS package) and add them batch by batch
to DB2's SQL. Returned data will go to SQLServer by DTS via
a data transform task.


We want to call this DTS package thru a stored procedure, so that
data transfer can be initated from any client machine. The packag will
be stored in server machine of SQL Server and the relevant drives
will be installed on that machine itself.


Thanks,
Hemant.

Hello Hemant_ng,

So there are two client master files and the one on B is subordinate to that
on A. You want to update the one on B with any changes from the one in A.

In SSIS this would be a breeze but DTS is not quite so feature rich so what
I would be inclined to do one of these

1.
Export from A --> B the master Client List.
Use TSL on B to UPDATE/INSERT /DELETE from its Master list

2.
On B create a linked server to A.
use TSQL to do the comparisons of keys between the two systems


If you adopt the approach in #1 it would be infintely better if you only
exported those clients you know to have changed in some way. Do you store
a marker against the rows to show when they changed? You could also employ
triggers to handle this for you and log to a table any actions.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks for the help, but
1. we cannot make any new table in A.
2. I doubt that we can create a linked server on an DB2 database
pointing to a SQL server db. Kindly tell me if I am wrong.

We dont have any details of the structure of A's table , nor can we
make
any changes in Syste A's database. There is no flag in A
to show which are B's customers as it is a corebanking product.
We want to avoid any changes in A as it will make
a scapegoat out of us for any and all problems that arise
in A later on. Moreover, we dont have a DB2 db admin or AS/400
sys admin to help us out of any muddle.........

I am looking at the Pre source data function of the
multi phase Data Pump and trying to write a ActiveX script
to do the work.

If ActiveX script can read thorugh A and reject non-relevant rows then,
we will transfer only B's rows to B. Script can match A's rows with
a global ADO recordset (with B's rows) and when it gets a match,
it will transfer that to B or a temp table.

Pls. tell me if it is liekly to succced.........

Hello Hemant_ng,

You create the linked server the other way around. it is from SQL Server
to DB2



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com