Hello, everyone,

In my SSIS package, I have a variable FilesInfo of Object data type that gets its values from the Full Result Set from  an Execute SQL task, which may or may not return any results. I have a Foreach loop container to do further processing.  If the values are not null, the package executes successfully. However, it throws an error when nothing returned fromthe SQL task.

To fix this, I want to add a Precedence Constraint to direct the flow only when FilesInfo is NOT NULL. Is there a way to do it? And if so, what is the exact syntax?

Thank you for your help in advance.

Regards

Then add a Script Task before the ForEach.

In the Script Task you can check in .net code http://sqlage.blogspot.ca/2013/07/ssis-how-to-read-object-type-variable.html if the recordset has any rows and assign your conditional test package variable a value you deem logically as a pass/no pass. It is just easier to say assign a -1 (as in VB.Net) to a false condition and then attest for it in the Precedence Constraint.

So some coding is necessary.

Just to be sure, are you using the Foreach From Variable Enumerator or Foreach ADO Enumerator?

With ADO enumerator, the Foreach Loop Task does not throw any error even if the resultset is empty.

If the error shows due to a task within the Foreach Loop Task (e.g. a File System Task) then you can let the Foreach Loop shred the object variable into scalar variables and use the expressions in the precedence constraints on the scalar variables within the Loop.

If the object variable is null then the shredded scalar variables would be empty too (but not necessarily null).

If you'd really want to redirect even before the loop then I don't know about any NULL check function for Objects. So maybe using a script task is the way to go.