June 9, 2014 at 12:51 am
Hi,
I have nearly 1 TB of CSV file data .I need to load this data into SQL database using SSIS.What is the best way to perform this(or how). Also if you could suggest the best practices while doing data load.
June 9, 2014 at 2:02 am
gstarsaini (6/9/2014)
Hi,I have nearly 1 TB of CSV file data .I need to load this data into SQL database using SSIS.What is the best way to perform this(or how). Also if you could suggest the best practices while doing data load.
First thought, minimally logged bulk insert and straight load without transformations (ELT instead of ETL).
Few questions;
1. Single or multiple files?
2. Single or multiple destinations?
3. One or more formats in the CSV?
4. Is bcp an option?
5. Is this a one off exercise?
😎
June 9, 2014 at 6:24 am
As there are 1 TB data suggested to avoid any constraints on the table. e.g. foreignkey, check constraints, indexes etc. This will speed up the process. You can do the BCP as well as SSIS depending on your requirement. If it is pretty simple use BCP.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 9, 2014 at 6:41 am
If the load needs to be repeated, I've always found a stored procedure that uses bulk insert to be very reliable. Create your format file and your staging table (bare bones, like it was suggested above), fire the bulk insert statement, do your validation and then load to your production tables. An added benefit is that you can schedule it to run as a job and have it send you email if it fails.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply