DTS import in multiple tables

Question

DTS import in multiple tables

Zaza

SSC-Addicted

Points: 493
More actions
February 7, 2005 at 11:35 am

#163335

Hi all,
I am new to DTS and I really need help in order to achieve this import, which is probably very easy for a 'connaisseur'..
I want to import member information, 4 fields to make it simple, (ID, name, addrline1, addrline2) from a CSV file to a SQL 7 database. The destination database has 2 tables: memberAccount and memberAddress
ID and Name will go in MemberAccount while Addrline1 and AddrLine2 will go in MemberAddress. The key in both table is the field ID.
If I use an import wizard, is there a way I can choose 2 tables for the destination (and create the relationship)?? Or do I need to create a Package?? If so, this is where it really gets complicated since I cannot find good examples of how to use tasks.. I saw that there is an 'Execute SQL Task' task where I can add SQL statement.. Should I use that?? If so, how do I link my csv file to this 'execute SQL task' task and then to the destination..
Can someone please help me with this?? All comments will be greatly appreciated..

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

You've got two options I can see straight off. Either way you won't be able to use the wizard.

1. Import your CSV file into a seperate table the database using the Datapump task. Then use two Execute SQL tasks to insert the data into the final tables. Importing into a seperate table first allows you to perform any data scrubbing before the final insert and is probably the standard methodology for this sort of task.

2. Use two Datapump tasks to import the required data into each table. With this method you would need to add appropriate workflow to ensure the Datapump tasks executed one after the other.

--------------------
Colt 45 - the original point and click interface

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

You will definately need to make sure to do the workflow as sometimes SQL Server gets a little out of wack when trying to access a file that is being used by another function (which would be SQL Server itself in this package). You can set it up in a way that the second datapump only goes if the first one was successfully completed.

Marvin

Marvin Dillard
Senior Consultant
Claraview Inc

Zaza SSC-Addicted Points: 493 More actions · Answer 3

Zaza

SSC-Addicted

Points: 493

February 8, 2005 at 9:34 am

#541159

Thanks!! I will try your ideas...

Teague Byrd Right there with Babe Points: 752 More actions · Answer 4

Marvin is right about dts getting out of whack dealing with open files. Once you get your transformation tasks set up, right click on the transformation line, click on WorkFlow Properties, Select the Options tab, and click the box that says "Close connection on completion".

This little box made ME go out of whack for several days till I finally found it. While testing, I would transfer a file successfully but when I tried to transfer it again, sometimes, it would work and sometimes not.

Teague