Import Text file Question

Question

Import Text file Question

runelords

Ten Centuries

Points: 1275
More actions
October 25, 2005 at 3:24 pm

#67921

I have a text file which is delimited. I want to used an ACtiveX script in a DTS package to import into a table. The hitch is that based on the first 10 characters in the row in the text file, it needs either update a row in the table if it exists or append a row in the table if it doesn't. I am somewhat new at this and am looking for help in getting me started in solving this request.

Arthur Lorenzini

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

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

corey lawson Hall of Fame Points: 3771 More actions · Answer 1

Well, check out these urls: http://www.sqldts.com/default.aspx?246 and http://www.sqldts.com/default.aspx?218.

What I would do is set up a VBScript task, that opens up the file to look at the first 10 chars in the file. You'll need to set up a FileSystemObject, a Stream object (that actually opens the file).

Then, set up two more tasks, one to append the data, and the other to update the data. Set up a "On Success" connector to one of the tasks, and an "On Failure" connector to the other. Based on those first 10 chars in the file in the first VBScript task, it'll either "succeed" or "fail" (you set the completion status programmatically for this task).

You might want to find a good VBScript reference. I like the one published by New Riders, but I haven't been able to find it for some time.

Otherwise, setting the next task to execute in a DTS VBSCript task is not trivial. What you see as the name of a task in the DTS designer is NOT the name of the task exposed to VBScript...

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

An (faster, more efficient) alternative is to import the text file into a working table, then use an update query with an inner join to update records that match, and an insert query with an inner join and a where clause to find the records that do not match.

Mark

runelords Ten Centuries Points: 1275 More actions · Answer 3

Do you have an example of how that would work? I understand how the transformation from the text file to the work table would work but I am a bit confused on the insert and update query.

Arthur Lorenzini

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

A simple example would be:

UPDATE MyTable
SET FldA=t2.FldA, FldB = t2.FldB
FROM MyTable t1 INNER JOIN WorkTable t2 on t1.PKey = t2.PKey
GO

INSERT MyTable (PKey, FldA, FldB)
SELECT t2.PKey, t2.FldA, t2.FldB
FROM WorkTable t2 LEFT JOIN MyTable t1 on t2.PKey = t1.PKey
WHERE t1.PKey IS NULL
GO

Note that you may want to put these in separate steps, and return the @@rowcount in each step to record how many records were updated and added.

Hope this helps

Mark