Source table filtering in DTS by target table values.
- From: "Hemant_ng" <hemant_ng@xxxxxxxxx>
- Date: 11 Mar 2006 03:12:54 -0800
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.
.
- Follow-Ups:
- Re: Source table filtering in DTS by target table values.
- From: Allan Mitchell
- Re: Source table filtering in DTS by target table values.
- Prev by Date: Re: SQL Job and DTS Failure
- Next by Date: Re: Source table filtering in DTS by target table values.
- Previous by thread: Re: Global Variable Passed As Parameter To Sproc Is Truncated
- Next by thread: Re: Source table filtering in DTS by target table values.
- Index(es):
Relevant Pages
|
