October 21, 2013 at 7:35 am
Hi all, im looking at importing a changing CSV File into a SQL Table, however the CSV file is not going to contain the same records each time, it could have new records or have an update to an existing record. I am looking for the best solutiuon to import the data from this CSV each time and either overwrite data based on a Value in one of the columns or insert a new line if a value does not already exist? Ive been looking at SSIS but this doesnt seem to be doing what i want it to.
Thanks
October 21, 2013 at 7:39 am
Is the structure of the CSV file going to be the same each time? (in other words, the same columns with the same data types?)
If yes, SSIS is able to handle your scenario pretty easily.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 21, 2013 at 7:43 am
Yeah the column Headings will remain the same. The trouble im having is if i import one CSV with a Reference of 123456 and then shortly after import another CSV with the same reference it's adding as a new line insted on overwriting the line that already exists with the new data?
October 21, 2013 at 7:47 am
chris.umpleby (10/21/2013)
Yeah the column Headings will remain the same. The trouble im having is if i import one CSV with a Reference of 123456 and then shortly after import another CSV with the same reference it's adding as a new line insted on overwriting the line that already exists with the new data?
I can't see what you see, so I don't know what you mean with a reference.
What I do think you need however is the incremental load design pattern:
SSIS Design Pattern - Incremental Loads
You can adapt the pattern to suit your needs. If it is however a completely useless suggestion, then you'll need to explain your situation a bit more.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 21, 2013 at 9:31 am
Thanks for the response it looks like this could work however the examples ive found are all related to importing data from another Table not a CSV File. The CSV File i have is also doing some data Conversion due to the format of the data in the file. All im looking for is if i get the file in at 09:00 with entries in this uploads to a table called ImportedData for example, when it looks at the file again at 10:00 (As this will have been overwritten by another app) it imports this data into the ImportedData Table but if one of the lines in the table with a unique reference number already exists in the table i dont want it to add as a new line i want it to overwrite the exist information within ImportedData Table with whats included within the new CSV File. I hope this makes sense?
October 21, 2013 at 12:37 pm
It makes sense. Just replace the source table with a flat file source.
Use a lookup component to check if you can find the unique reference number.
Matches are updates, non-matches are inserts.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply