SSIS SQL Execute task error caused by single row result set but no rows were returned

Question

In SSIS 2008 I have an Execute SQL Task in a Foreach loop that will fetch one single result value from a table and puts it in a variable for File System Task. The SQL code is similar to:

SELECT productcode
FROM products
WHERE eancode = ?

The code will return the productcode from table products if it matches the corresponding eancode. The ? is a variable in SSIS derived from an image filename.

Basically, the package (loop+sqltask+FStask) will change the filename from eancode to productcode.

Sometimes the package fails because the SQL task tries to get the productcode but cannot return a result. This happens if eancode does not exist in the table.

What can be done to deal with such an error so the loop doesn't stop?

I thought of these possible solutions:

1. Use ISNULL(productcode,'replacement') is not an option. The result set will be used into a variable which will be used to rename the filename of the image. This will cause every no result image to get the 'replacement' name. Also, it just won't return anything, not even the isnull replacement.

2. Change the properties settings of the exec SQL task or ForEach Loop, so it skips to the next but which one and to what?

3. Add a failed precedence constraint from sql task to another (child) task. But what task should it lead to? It basically has to nothing if no result set in sql task.

4. Change the constraint to Complete, but this will cause the FileSystem task to give an error since it is missing values for the Destination filepathname. If the sql task fails it should not rename the file in next task.

The ideal situation would be if the sql task fails because of no result set, the current loop would leave the SQL step as is and go on with the next round.

Thanks

Answer 1

You can evaluate the variable value that is populated from the query and add a condition to the success constraint to check the value of the variable. I put together a simple example, but hopefully it helps.

I used this function:

LEN(@[User::Variable]) > 0

Answer 2

This should guarantee a row is returned every time, then you can work out how to ignore the NULL in a subsequent step.

But SSIS might be too dumb to work out where the parameter goes when you hit the parameter button.

SELECT TOP 1 productcode
FROM (
SELECT productcode
FROM products
WHERE eancode = ?
UNION ALL 
SELECT NULL
) As F
ORDER BY productcode DESC