best practices on maxinsertcommitsize

Hi,

I wonder if anyone knows what would be the best case scenario for the property 'maxinsertcommitsize' for the sql destination task if I want to load 6m records into a target. Is the best setting 0 (try loading all in one batch) or should I choose a different value for example 1000000 per batch?

Thanks,

Marc

This setting is up to you. How big of a batch do you want to insert? If you encounter an error in the load, do you want to roll back ALL records, or just the batch?

There is no "best practice" because this is user dependent. Everyone's situation is different.|||

Ok, but does it affect performance? For our situation the following applies:

- We don't care how big the batch will be as long as it is optimized for maximum performance
- If we encounter an error in the load it doesn't matter if ALL records roll back or just the batch.

|||I don't think there will be much of a performance difference. The one thing to watch out for in a big batch is the potential for filling up the transaction log.

Try different settings and report your results back to us.|||Actually there may be a performance difference between using batches and not. When SQL Server commits a batch it has to update any affected indexes. 6M records could be a good deal of work. I've seen it take over an hour to commit a batch that size when indexes are involved. By using smaller batches, SQL Server can get started on this work while SSIS is still sending it rows. But that advantage depends on how long your SSIS process takes. If it can generate those 6M in 30 seconds, then giving SQL a head start isn't going to do much good.

Beginer Problem: Is there any way to create the destination file at runtime?

I have to generate flat files from SQL Server tables. Its a backup package for few tables which will be run daily. I want the names of the destination flat files to be automatically generated at run time.

I created an expression for the Flat file destination connection and appended the date and the time to the flat file name. For today my file name is 'table02-12-2007.txt'. for tomorrow it should be 'table02-13-2007'. When I evaluate the expression it shows the correct value.

But when i run the package, I receive an error indicating - Destination file is not found. Error is because the destination file is not present at runtime and I was hoping that the package will be create the file on the fly.

Is there any way to create the destination file at runtime? Or Am I missing a step?

Any help will be greatly appreciated.

Thanks

Hi Don,

You probably need to set the DelayValidation property for the package to True. Do this by clicking on the Control Flow and hitting F4 for properties.

One issue here is the destination file for the Flat File Connection Manager does not exist - by design. You have to configure the package so it will stop looking for it - which is what occurs during validation.

Hope this helps,
Andy

|||

Thanks Andy for your quick response.

I changed the delay validation but that didnt work. There were spaces in destination path "C:\My Folder\My Files" which was not allowed.

Is there any way we can have spaces in the path or connection string for flat file?

Thanks

|||

Hi Don,

I believe you can enclose the file name in quotes.

Hope this helps,
Andy