SSIS and SQLite

Question

hey guys i want to use a ssis task to generate daily sqlite dbs... the ssis job exports large amounts of data to sqlite.

the problem i am facing is ssis opens and closes a transaction for every insert making this task painfully slow

is there a way i can instruct ssis to open a transaction at the beginning of the task, perform thousands of inserts and finally close the transaction

i know i can easily do this using the system.data.sqlite dll and c# code but the sole reason i want to use ssis is to have a solution with bare minimum code

can anybody help please... thanks in advance

Answer 1

Although i am not sure what kind of Transformation/Destination components you are using in your data flow task, i think that you are using OLE-DB-Command to insert values to the destination. The OLE-DB-Command runs statements for each row.

So if you know you are dealing with more than just a couple hundred rows per run then I would highly suggest using OLE-DB-Destination which has options such as fast-load that control how many rows would be inserted in a batch. (look for Rows Per Batch and Maximum Insert Commit Size options)

You may need to use a staging table to be able to use OLE-DB-Destination in your case but it may be much more performant than your current implementation (assuming OLE-DB Command).