August 12, 2004 at 8:08 am
I am new to SQL, so I apologize a head of time if this is not the right forum for this question.
I have a DTS package set up to run every 24 hours. It ties back to a .csv file created by an ERP package. Currently, the DTS package appends the data in the .csv file to the table in question. Is there a way to have the package replace portions of the data while retaining the rest of the data in the table?
Here is what I am trying to accomplish. The ERP creates a .csv file for a rolling three day period of time. Ideally, I would like to have the DTS package set up so that when it loads the current .csv file, it will replace all of the data for that rolling period of time. Everything outside of that period of time in the table should be left alone. (Every row in the table has a date field.)
Thanks for your help!
-Rachel
August 12, 2004 at 12:20 pm
Well, I think my response to this would be similar to another response I just posted.
I don't think you could do this all in one step. Nor would you want to if that required doing row-by-row processing, which is much slower than set-based.
So, it seems a solution would be to load the csv file into a staging table. Then, update the final destination table from the staging table. You could first find the min/max datetimes from the staging table, then delete rows in the final table within those dates. Then copy over the rows between those dates from the staging table. Something along those lines.
August 12, 2004 at 12:59 pm
Thank you for the advice. If I went this route, could it be scheduled, or would it be better to go through the steps manually?
August 12, 2004 at 1:08 pm
It seems putting all of these steps in a DTS package, and then scheduling it is the way to go. Perhaps the only problem might be if the ERP application did not successfully place the csv file. So, you may need to check for the existence of that file, and have the package notify you if the file isn't there. I'd also suggest keeping that csv file historically, rather than allowing it to get overwritten (assuming it isn't a MASSIVE file that would gobble up disk space). So, let's say you load the csv file (call it csvFile.txt) today. After succesful loading, you could could rename it to csvFile_D20040812.txt, and move it to a different folder. That way, you could trace back if there is a problem.
August 13, 2004 at 7:57 am
This could easily be scheduled to work automatically.
You can use the staging table, which is fine, or you could delete the portion of the permanent table which will be replaced by the incoming file. Your situation sounds pretty predictable. The staging table would be necessary if that weren't the case.
Use an Execute SQL task to delete all the data two days or younger, then import the file.
August 13, 2004 at 9:33 am
As I said in my original post, I am new to SQL. Could any of you recommend a good reference for designing DTS packages?
Thanks again!
August 13, 2004 at 10:36 am
I use "Professional SQL Server 2000 DTS", but mostly as a reference rather than a manual. It's a decent book.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply