Recovery Model to be used for a Data Flow Task

Hello there

I have a SSIS 2005 DTSX Package which has several Data flow tasks which directly dump data between tables from 2 different databases.

Suppose if my destination database is named, DestinationDB, what recovery model should I select for this database so that my ldf file size does not explode? I want to keep the ldf file as small in size as possible.

1. Currently I have used Simple Recovery model, and the ldf file size goes to around 80 GB. (Inserting around 200 million rows)

2. Will Bulk Logged model be a better option?

Also what does a SSIS 2005 Data flow task use internally? (Insert operation or some sort of Bulk Insert between DBs)

Please help.

The fast load option on the OLE DB destination performs a bulk load.|||

Thanks for the response.

Does the fast load option keep the ldf file size to a minimum?

|||This probably isn't the correct forum for you, but I believe you'll want "bulk-logged recovery" so as not to grow the LDF file. Or split your data into smaller chunks using the MaximumInsertCommitSize setting on the OLE DB Destination.

Records not commiting - please help!

So, I have this SSIS package that basically moves records from one table into another table in the same DB. I’m running it on the actual server and everything seems to work. The data-flow task lights up yellow and visual studio increments the number of records passed through as if they were running perfectly. However, when I go to look at the data,it’s not in there! There are no errors reported ad the event log doesn’t show anything that would lead me to think anything went wrong. Anyone ever run into this issue? I’m wracking my brain on this one and am completely stumped. Any help would really be appreciated

Do you wait until the process completes (goes green) to look for the records?|||

I've let it go all the way to the end and tried to look at the data while the move was hapening. I've played with all the OLE DB destination options as well with no luck. I've had batch sizes of anywhere from 1 to 1000 with no luck. SQL server activity monitor lists the bulk insert but it never leaves the "runnable" state.

I am using the OLE DB data source and destination and have one data conversion transform task between them.

|||Are you sure the records are valid? Double click on the OLE DB Destination and select "Error output" and ensure that it is set to "Fail component" instead of "Ignore failure."|||

Try using a destination table which has no rows. Then run your package, and see if the destination table has any rows now. That will help you identify if rows are not being inserted at all, or were inserted but some other failure happened.

|||

so, the destination table's totally empty. I ran the package all the way through and it's still not working!!! I can tell somethings happening since my drive's filling up as the thing trudges along. Very, very irritating~

|||

JimiTooCool wrote:

so, the destination table's totally empty. I ran the package all the way through and it's still not working!!! I can tell somethings happening since my drive's filling up as the thing trudges along. Very, very irritating~

Did you make sure that the error handling of the OLE DB destination was set to "Fail component"?