Mapping multiple source tables and mutilple destination in a Data Transformation Task or DDQ

Question

Mapping multiple source tables and mutilple destination in a Data Transformation Task or DDQ

jones bastin

Old Hand

Points: 348
More actions
July 15, 2005 at 3:02 am

#186599

Hi
I like to create a Generic DTS package.(ie) i need to pass source table name and destination table name dynamicaly in a Data Transformation task or Data Driven Query .
I am able to pass a source table name dynaimcally provided all the columns of source table are same.i find it difficult to pass destination table name.
is it possible to do this different transformation ?Could u pls help me out ?
Need for this Requirement :
Requirement is to import data from 10 different tables to 10 differenet destination .To do this i need to create 10 different DDQ or Data transformation task which eats 10 different connections.

Thanks in Advance
Jones

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

urvashi SSC Enthusiast Points: 115 More actions · Answer 1

I dont understand why u need 10 different connection , why can u not use the same connection for every data driven query???

and i am not clear with your question can u explain whether your destination table for each source table wud be the same or the destination table will have different columns every time

regards

jones bastin Old Hand Points: 348 More actions · Answer 2

Hi urvashi

sorry for the confusion

I have following tables. need to import from source to Destination using DTS

Source Destination

Emp ----- EmpInfo (emp and empinfo Struture are same )

Job ----- Jobinfo (Job and Jobinfo Struture are same )

Salary ----- SalaryInfo (Salary and SalaryInfo Struture are same )

But table Emp , job , salaray are different in structure

To do this , i need three Data Driven Query (DDQ).My idea is to store the source table name and destination table name in a separate table called StoD .

Try to use a single DDQ and loop through records from table StoD. so all import tranformation can be achieved in a single DDQ.

is it possible to do ?

Thanks in Advance

Jones

urvashi SSC Enthusiast Points: 115 More actions · Answer 3

Since the structure of the three tables are different so you will need three different tasks and why do you want to store the table names in a separate table , if only to make the package generic then the better way of doing this is you make three data transform task and for each task create two dynamic properties task each of which will provide the source and destination table name respectively either through a constant, global variable or through a ini file (I wud recommend ini file) whatever suits you better.

Hope this makes sense

Regards

Urvashi

jones bastin Old Hand Points: 348 More actions · Answer 4

Hi urvashi

Thanks a lot . let me try out this approach .

Thanks

Jones