CODEWITHSUNDEEP
sql-serverssisetl
Dizzy49
Dizzy49

Reputation: 1530

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

Upvotes: 3

Views: 10917

Answers (3)

Carlos
Carlos

Reputation: 706

It just needs to use UNION another row empty always return a value

Like it if work for you

SELECT foo As nameA, fa AS nameB
UNION ALL
SELECT NULL AS nameA, NULL AS nameB

And then you can validated (line) contrained if var is null no allow enter image description here

Upvotes: 0

Hadi
Hadi

Reputation: 37368

If you want to check if a row exists then you should use Count as @jradich1234 mentioned.

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

If you are looking to check if a row exists and to store the id in a variable to use it later in the package, first you have to use TOP 1 since you selecting a single row result set and you can use a similar logic:

DECLARE @Filename VARCHAR(4000) = ?

IF EXISTS(SELECT 1 FROM PORG_Files WHERE filename = @Filename)
    SELECT TOP 1 id FROM PORG_Files WHERE filename = @Filename
ELSE
    SELECT 0 as id

Then if id = 0 then no rows exists.

Upvotes: 2

jradich1234
jradich1234

Reputation: 1425

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 = ?

Upvotes: 5

Related Questions