question

DBADave avatar image
DBADave asked

SSIS and For Each Loop Delay

Greetings fellow SSIS Peeps, I have a unique situation and wanted to ask some of my counterparts for some advice. First some background, I have a base package that contains a for each loop, the purpose of this loop is to iterate through a folder to call other SSIS packages from a structured file. I am using SQL 2008 (non R2). I'm finding that when each package is called within the for each loop there is a ~45 second delay, (probably because of validation) before the package executes, each package once executed runs in a matter of milliseconds, the problem I'm trying to address is if I aggregate the delay before each package executes then the overall execution time for the entire ETL export process is significant because the loop calls 65 individual packages. I've tried setting delay-validation to true with no change. Does anyone have any other ideas on how I could turn off validation completely or other ideas on how to have the called SSIS package execute in less than 45 seconds ? I'd greatly appreciate anyone's input or suggestions. Many Thanks, Dave Fraser Sr SQL DBA Tech Data Corp
ssisforeach-loopfor
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
The Delayed validation setting is related to the IDE. When it is set to true, then the package is not validated during development and allows you to modify the package even the data sources are not available at design time on development machine for what ever reason (security, network, edit when not connected to network on notebook etc). Anyway the 45 seconds delay is an extreme. Generally the validation took milliseconds, if you have correctly setup all the connections etc. There is no reason for such long delay. Only in case of some slow connections or network delays when accessing files on remote machine. (eg. not correctly written UNC path when in some cases a resolution can take some time). Also some inappropriate setup of logging in the package could cause such delays.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Pavel, Many Thanks for the response. I've verified that all network settings and configs are good. I haven't looked at the logging setup yet but that's my next task. Thanks Again, DBADave
0 Likes 0 ·
Dear Pavel, I face the same problem, please if you solved it tell me Thanks in advance
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.