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:
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.
Change the properties settings of the exec SQL task or ForEach Loop, so it skips to the next but which one and to what?
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.
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.
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