October 25, 2005 at 3:24 pm
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
October 26, 2005 at 12:43 am
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...
October 26, 2005 at 6:31 am
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
October 26, 2005 at 7:22 am
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
October 26, 2005 at 8:44 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy