c0fee
asked on
SQL 2005 import script / SSIS package to import csv file and check for existing records
I've only had elementary exposure to building SSIS packages and BCP scripts to automate data import, so any suggestions with this would be greatly appreciated.
I'd like to be able to automate a csv file import into a SQL table (would not contain more than 2K records) but only insert if the record's primary key does not already exist. If there is an existing record with the same primary key as the record in the csv file, then update the record.
The need seems simple enough and must be something that is widely executed... I just don't know where to begin.
Thanks,
Mimi
I'd like to be able to automate a csv file import into a SQL table (would not contain more than 2K records) but only insert if the record's primary key does not already exist. If there is an existing record with the same primary key as the record in the csv file, then update the record.
The need seems simple enough and must be something that is widely executed... I just don't know where to begin.
Thanks,
Mimi
try this
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
http://huddledmasses.org/importing-csv-files-to-ms-sql-2005/
http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/
http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
http://huddledmasses.org/importing-csv-files-to-ms-sql-2005/
http://decipherinfosys.wordpress.com/2008/07/28/ssis-importing-data-from-a-text-file-using-a-package/
http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm
this is my SSIS package to load detached email files to database.
it is a hard too explain them all in a few words.
it just gives you an idea what it can be.
ssis.jpg
it is a hard too explain them all in a few words.
it just gives you an idea what it can be.
ssis.jpg
ASKER
acperkins - for the SQL script, do I need to curse through the temp table records and pass the values in an "if exists then update else insert" statement? If I'm doing this for 2K records, do I have to worry about performance, table locks, etc.?
rashm and timexist - thanks for the links and the diagram. It will be a useful reference, but at this stage, I'm not so concerned about how to build a process. But rather how to parse through the CSV file and determine whether I should execute an UPDATE vs. an INSERT- then incorporate into an automated SSIS package.
rashm and timexist - thanks for the links and the diagram. It will be a useful reference, but at this stage, I'm not so concerned about how to build a process. But rather how to parse through the CSV file and determine whether I should execute an UPDATE vs. an INSERT- then incorporate into an automated SSIS package.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Implemented the staging table to get this to work.
1. Import the whole table into a staging table.
2. Once imported, validate the data and only import the rows that do not exist using a trivial SQL query.