Search code examples
sql-serverssisetlforeach-loop-container

SSIS For Loop Container Runs Successfully But Data Flow Tasks and Scripts Inside of the Loop Intermittently Run

I've done some research on SO but having a tough time finding a solution (it might be the way I am searching), so please direct me to an appropriate post if you know of one. I am also by no means an SSIS guru.

I have an SSIS For Loop that always completes successfully, but the various data flow and script tasks inside of the loop intermittently run (they all do run successfully sometimes, but when they don't run, they are left with no indicator that they ran...no green/red/yellow checkmarks, just blank). It seems like the evalexpression in the loop is being interpreted incorrectly. If I rerun the package manually in VS, sometimes it returns data and sometimes it doesn't. This package is deployed to an SSISDB and the SQL Server Agent Job runs successfully each night, but again, intermittently returns or doesn't return the previous days data.

The loop is setup based on dates. The initexp assigns it yesterday's date, the assign expression increments the date variable by a day after it runs successfully, and then should evaluate to false. See image

forlooplogic

The @begindate variable's expression is 

= dateadd("d",-1,getdate())

Here is an image of the For Loop and the items inside of it. In case it matters, the tasks inside are just going out and hitting an api, storing the result in a variable, and then process it via various script tasks.

forloopcontents

Any advisable next steps for troubleshooting? Anything I am overlooking / missing?

Picture of successful run with no data with variable information

successnodata

Solution

  • Use @[System::StartTime] instead of GETDATE()

    Since GETDATE() is a function which is evaluated each time it is called, it may cause some issues if you use it inside a for loop expression.

    Using dateadd("d",-1,GETDATE()) in the Eval expression means that it is dynamic and doesn't contains a fixed value.

    You can benefit from the system variable @[System::StartTime] which contains the datetime of the package execution. The expression used to evaluate @[User::begindate] will be:

    = dateadd("d",-1, @[System::StartTime])

    InitExpression

    @startdate = @begindate

    EvalExpression

    @startdate <= dateadd("d",-1,@[System::StartTime])

    Note that if @[System::StartTime] and @StartTime didn't worked in the expression then add a new variable of type DateTime and Evaluate is as expression = @[System::StartTime] then use this variable in the EvalExpression.

    AssignExpression

    @startdate = dateadd("d",1,@startdate)