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
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.
Dear Pavel, I face the same problem, please if you solved it tell me Thanks in advance
1 Person is following this question.
