How to import one text file to multiple tables by using DTS

[lucyc]

I have a fixed length text file which contains orders and products information. I would like to import this text file and split it so order info will be imported to order table and product info will go to product table.
The text file layout is;

orderNum ProductNum ProductDesc UnitPrice Unit
123 P111 AA battery 2 1
123 P112 AAA battery 5 3
188 P111 AA battery 2 10

Thanks.

 

[dbomrrsm]

I would go for bringing the table into a table in SQL Server then do two DTS transform tasks to split the table into your order and product tables by specifying a quesy from the imported table to the orders and product tables.

[blue]DBomrrsm[/blue]

 

[ks2000]

In the DTS Transform Wizard.
After selecting Souce and Destination, you just click on the Transformation button. Column Mappings and Transformations Window will appear. just select the Source column and Destination columns which are related to order data. so only order data will go in to order table. Again repeat the same for product also so only productin data will insert into product table. If you want to re-use this again and again then save the DTS package and re-use when ever you want.

Good Luck
Gopala Krishna Kakani