Search code examples
sql-serverssisetl

SSIS Execute SQL Task Error "Single Row result set is specified, but no rows were returned."

I am attempting what I thought was a relatively easy thing. I use a SQL task to look for a filename in a table. If it exists, do something, if not, do nothing.

Here is my setup in SSIS:

SSIS Setup

My SQL Statement in 'File Exists in Table' is as follows, and ResultSet is 'Single Row':

SELECT ISNULL(id,0) as id FROM PORG_Files WHERE filename = ?

enter image description here

enter image description here

My Constraint is:

enter image description here

When I run it, there are no files in the table yet, so it should return nothing. I've tried ISNULL and COALESCE to set a value. I receive the following error:

Error: 0xC002F309 at File Exist in Table, Execute SQL Task: An error occurred while assigning a value to variable "id": "Single Row result set is specified, but no rows were returned.".

Not sure how to fix this. The ISNULL and COALESCE are the things suggestions found in SO and on MSDN

Solution

  • Try changing your SQL statement to a COUNT then your comparison expression would read @ID > 0. So, if you have files that match your pattern the count will be greater than 0 if there are no files it will return 0.

     SELECT COUNT(id) as id FROM PORG_Files WHERE filename = ?