May 20, 2015 at 3:35 pm
I have a pretty simple SSIS package that fast loads a 100 million record table into a SQL Server 2008 table on a daily basis. This normally runs fine and completes in about 1 hour. As this is perhaps one of our largest running SSIS packages, about once every 2-3 weeks this SSIS will fail/drop connection. Once it fails, the large number of records will start rolling back. This rollback process can take 1+ hours so I cannot even restart the failed SSIS package immediately. This is a problem.
I am looking for a solution or option so I do not have to wait on that rollback to restart this particular, long running SSIS package. Is there an option/setting to leave the partial data set committed and not rollback? Then I could just restart the SSIS package immediately or set it the SSIS to auto-restart 1 time on failure. The first step in the SSIS does a truncate of the destination table.
Thanks in advance!
May 21, 2015 at 8:31 am
If I simply change the "FastLoadMaxInsertCommitSize" (or a similar option) to say 200,000 records in SSIS will this not cause the entire table to rollback if it fails after say loading 50 million records? In other words will the already committed records remain committed or will it rollback anyway since the SSIS failed half way through the load? I am not using anything fancy (like transactions) in this SSIS (just source to data conversion step to destination).
May 21, 2015 at 10:26 am
Yes, however, depending on how your table is configured then you may get into other issues in terms of performance if you're utilising the minimal logging advantages of the fsat load method.
What recovery model are you using, and is the target table a heap or is it indexed? If its a heap with no non clustered indexes, then you aren't going to have any issues with t-log growth. However, if it does have indexes, then after the first batch is committed, each subsequent batch will then be fully logged, which could end up with some t-log growth issues and performance issues for your import.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply