June 7, 2004 at 10:13 am
Hi I'm new too in DTS.
I need to import data from txt files. But the data may be already in my DB.
How to do to manage the errors / ignore them but I don"t want to have twice the same records ?
Tom
June 7, 2004 at 3:39 pm
Hi Tom, this is one idea...
1.import the txt file or files to a temporary table.
2.using Execute sql task you can delete all the records that already exist in you master table.
3.import the records left in the temporary table to the master table
I hope this help
Johnny..
June 8, 2004 at 1:19 am
Yes it is. but i thougth that it exists a function in DTS to do such a thing !
Thanks
Tom
June 8, 2004 at 3:04 am
I can only see three ways of skipping the rows without using a temporary table.
1. Use an activex script on the transformation skipping the duplicate rows with 'Main = DTSTransformStat_SkipRow'. However, you cannot use the select clause in activex so I don't think it is possible. If it is, it will probably be a lot of coding to get it to work.
Alternatively, you can put a trigger on the database to delete the records if they already exist.
Or, you can put together a stored procedure that your dts package calls after the import to delete the records.
All in all, I'd have to say the temporary table method is the simplest and most effective way.
June 8, 2004 at 3:36 am
Ok I think you're right.
But I have another question :
when you write 'temporary table' is it real sql temporary tables (##temp or #temp) ?
If yes, how to generate it in the DTS ?
(the connection does't seem to display me temp tables)
tom
June 8, 2004 at 3:54 am
The #temp table is only available to the session that it is created in. You can create the properties of the dts task dynamically using a dynamic properties task, but again it is a lot of work. Also, you may want to see the data you are getting out to make sure it looks right etc.
I would:
1. Create a table like tmpStagingTable.
2. Truncate it before you start. Don't drop it and recreate it.
3. BCP the data from the text file into the tmptable
4. Run a query to insert records that are not duplicates.
You can put it all in one dts package to check each step.
Good luck
June 8, 2004 at 5:39 am
Another possibility is using a data-driven query..
With this query you can create lookups to determine if a record exists and if so skip the row.
Greetz,
Kalin
June 8, 2004 at 5:39 am
ok thanks, I have to create 10 temptables. Not dynamicaly but it will work.
thanks for your time !
Tom
June 8, 2004 at 5:41 am
which one ?
T
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply