I am new to SSIS. I have a very basic question regarding bulk loading. My aim to find a method to speed up the bulk loading process. My situation is very simple. I have a flat-file which is of the size 934 MB (when inserted table contains 4423763 rows). I am suppose to load this flat-file data into a table(containing around 60 columns) without any transformation.
I loaded this using 3 methods: BCP utility - 2 hrs 51 mins T-sql bulk insert query - 2 hrs 10 mins SSIS bulk insert task with tablock - 01 hr 44 mins
I know the time taken for the loading process is quite abnormal. Please suggest some tips or techniques to speed up this process.
Remove indexes from the table. Make sure the database is bulk logged or simple.
Even for that you have less than 1GB of data - are you inserting from a remote location? If so it is probably the network that is the issue - copy the file somewhere more local first.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
As long as it is not using full recovery model you should be ok. Simple truncates the log on checkpoint so you don't have to do tr log backups. Bulk logged allows bulk operations with very little logging but still allows log backups.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Hi, When i disabled the clustered primary key of a table, the foreign key constraints on the tables that are referencing the disabled primary key are disabled as well. When i enable back that clustered primary key, the foreign key constraints still remain disabled. What do i do?
Fastest method for bulk loading?
Posted - 01/07/2013 : 00:41:36
