CODEWITHSUNDEEP
sql-serverforeachssis
tember
tember

Reputation: 1496

SSIS Foreach Loop failure

I have created a lookup for a list of IDs and a subsequent Foreach loop to run an sql stmt for each ID.

enter image description here

My variable for catching the list of IDs is called MissingRecordIDs and is of type Object. In the Foreach container I map each value to a variable called RecordID of type Int32. No fancy scripts - I followed these instructions: https://www.simple-talk.com/sql/ssis/implementing-foreach-looping-logic-in-ssis-/ (without the file loading part - I am just running an SQL stmt).

It runs fine from within SSIS, but when I deploy it my Integration Services Catalogue in MSSQL it fails.

This is the error I get when running from SQL Mgt Studio:
enter image description here

I thought I could just put a Precendence Constraint after MissingRecordsIDs get filled to check for NULL and skip the Foreach loop if necessary - but I can't figure out how to check for NULL in an Object variable?

Here is the Variable declaration and Object getting enumerated: enter image description here

And here is the Variable mapping: enter image description here

The SQL stmt that is in 'Lookup missing Orders':

 select  distinct cast(od.order_id as int) as order_id
 from invman_staging.staging.invman_OrderDetails_cdc od
 LEFT OUTER JOIN invman_staging.staging.invman_Orders_cdc o
 on o.order_id = od.order_id and o.BatchID = ?
 where od.BatchID =  ?
 and o.order_id is null
 and od.order_id is not null

In the current environment this query returns nothing - there are no missing Orders, so I don't want to go into the 'Foreach Order Loop' at all.

Upvotes: 1

Views: 5809

Answers (2)

Pete Kozak
Pete Kozak

Reputation: 623

In my case it wasn't NULL causing the problem, the ID value which I loaded from database was stored as nvarchar(50), even if it was a integer, I attempted to use it as integer in SSIS and it kept giving me the same error message, this worked for me:

SELECT CAST(id as INT) FROM dbo.Table

Upvotes: 0

William C.
William C.

Reputation: 416

This is a known issue Microsoft is aware of: https://connect.microsoft.com/SQLServer/feedback/details/742282/ssis-2012-wont-assign-null-values-from-sql-queries-to-variables-of-type-string-inside-a-foreach-loop

I would suggest to add an ISNULL(RecordID, 0) to the query as well as set an expression to the component "Load missing Orders" in order to enable it only when RecordID != 0.

Upvotes: 3

Related Questions