May 28, 2016 at 11:12 am
Hi All,
My SSIS package getting blocked at Pre Execure for more than 14 hours which is not at all acceptable in real time.
My source pulling 90 millions of records, and passing through lookup which got 5 millons of records.
can you please suggest me how to optimize SSIS package
Note:
Set Delay validation True on DFT's and connection manager level.
Set BufferTempStoragePath property to server path
Set DefaultBufferMaxRows to 100000
Thanks,
kumar
May 29, 2016 at 3:25 pm
phanithota26 (5/28/2016)
Hi All,My SSIS package getting blocked at Pre Execure for more than 14 hours which is not at all acceptable in real time.
My source pulling 90 millions of records, and passing through lookup which got 5 millons of records.
can you please suggest me how to optimize SSIS package
Note:
Set Delay validation True on DFT's and connection manager level.
Set BufferTempStoragePath property to server path
Set DefaultBufferMaxRows to 100000
Thanks,
kumar
without more details its hard to give exact options but try the following ones.
1 - if the lookup can be done directly at the source do it.
2 - copy the 5million rows to the source and do the lookup on the source
3 - if the lookup can be done at the destination do it (only if 1 and 2 not possible at all)
4 - split the dataflow into several - load into temporary tables, use partitioning and join everything at the end.
if your source has any LOB type it will always be slower than usual - not much you can do to prevent it apart from the splitting of data flows, or if your source and SSIS machine have enough power and allow for it, zip those LOBS before transferring.
it is also possible that you are getting "blocked" when loading the lookup table or on the execute phase if the source has to pre-sort / calculate data before releasing the records.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply