Efficient way to transfer huge amount of records

Hi All,

I used a data flow task, and when trying to transfer data from a OLE DB Source (records ~ 75 lac) to a destination OLE DB Source, SSIS fails at the middle giving an error saying the Transaction log got filled, try again after clearing the same.

My query is what is the most efficient way to transfer say records more than 50 lac ensuring that it doesn't fail in the middle?

Thanks in Advance,

Mithun.

To avoid the error you need to increase transaction log size in SQL Server - it is SQL's transaction log, not SSIS'. I believe you can also decrease the batch size in OLE DB destination properties.

The most efficient way is SQL Destination - if the destination server is on the same machine where package runs, it is faster than OLE DB Destination.|||

Thanks for the comments!! I'll try out the options.

|||

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

|||

enric vives wrote:

Michael,

But if you need to copy from .xls source into a table you can't use Sql Destination.

Correct me if I'm going wrong.

That's wrong. The source of the data is irrelevant. There are some pre-requisites to using SQL Server Destination and the April 2006 drop of BOL tells you all you need to know.

-Jamie