December 2, 2011 at 11:15 am
When a flat file is being loaded from Sql table, it taking some time around 20 mins. Is there a way I can reduce this time to 7-10 mins. I have no idea how is could be done, please share me any konwledge related to this. Thank you
December 2, 2011 at 11:46 am
There's lots of things you can try.
First, make sure your code is all optimised. That means, your SQL query that is loading the data only takes in the fields you are using. Next, make sure that the field sizes that you're outputting are set correctly - don't output a VARCHAR(5000) field when the values are limited to 20 characters. Set the output file parameters to have a field of 20 characters, and then use the data conversion transform to fix your data up.
Make sure you don't have any blocking components in your data flow task. SSIS is designed to pipeline data from the source to the destination. If you put things like SQL Command transforms or aggregate transforms, then you restrict the input flow, which could cause major slowdowns if your computer isn't powerful enough to handle it.
Finally, if none of this helps, you could try changing it from a flat file destination to a raw file destination. Raw files process much faster, although they have some limitations that flat files do not.
December 5, 2011 at 2:07 am
Adding some more points to tune your performance in extracting the data. In Data Flow component there is an property DefaultBufferMaxRows and DefaultBufferSize, Normally Default buffer size will me 10 MB and Default Buffer max rows will be 10000 records, increase Default Buffer size depend on your machine and calculate the one row size and 10MB/one row size for example if you get 20000 reduce 10% for the safe side so that 18000 records will be pumped to flat file in one shot instead of 10000 records it will improve 30% to 40% on extracting to flat file, If you have more data.:w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply