title: SSIS SQL Execute task error caused by single row result set but no rows were returned
body: <p>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:</p>
  
  <pre><code>SELECT productcode
  FROM products
  WHERE eancode = ?
  </code></pre>
  
  <p>The code will return the productcode from table products if it matches the corresponding eancode. The <code>?</code> is a variable in SSIS derived from an image filename.</p>
  
  <p>Basically, the package (loop+sqltask+FStask) will change the filename from eancode to productcode. </p>
  
  <p>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. </p>
  
  <p><strong>What can be done to deal with such an error so the loop doesn't stop?</strong></p>
  
  <p>I thought of these possible solutions:</p>
  
  <ol>
  <li><p>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.</p></li>
  <li><p>Change the properties settings of the exec SQL task or ForEach Loop,
  so it skips to the next but which one and to what?</p></li>
  <li><p>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. </p></li>
  <li><p>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. </p></li>
  </ol>
  
  <p>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.  </p>
  
  <p>Thanks</p>