SSIS Execute SQL Task error

Question

I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "Declare @POID as Varchar(50) Set @POID = 636268 ..." failed with the following error: "Unable to populate result columns for single row result type. The query returned an empty result set.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task 1

The package has a single Execute SQL task with the properties listed below :

General Properties Result Set : Single Row

ConnectionType : OLEDB Connection : Connected to Server SQLSourceType : Direct Input SQL Statement :

Declare @POID as Varchar(50)
Set @POID = 0
SELECT DISTINCT BizTalk_POA_HEADER.PONUMBER, FAN_Suppliers.SupplierName, FAN_Company_Details.CompanyName, FAN_Company_Details.[PrimaryEmail], BizTalk_POA_HEADER.[DeliveryDate]
FROM  BizTalk_POA_HEADER 
INNER JOIN FAN_PO_Details ON BizTalk_POA_HEADER.PONUMBER = FAN_PO_Details.PoNumber                                                                                                                                                                                                                                                                                                       
INNER JOIN FAN_PO ON FAN_PO_Details.PurchaseOrderID = FAN_PO.PurchaseOrderID     
INNER JOIN FAN_SupplierDetails ON FAN_PO.SupplierDetailsID =     FAN_SupplierDetails.SuppliersDetailsID 
INNER JOIN FAN_Suppliers ON FAN_SupplierDetails.SupplierID = FAN_Suppliers.SupplierID 
INNER JOIN FAN_Company_Details ON FAN_PO.CompanyID = FAN_Company_Details.CompanyDetailsID

WHERE    (BizTalk_POA_HEADER.PONUMBER = @POID)**

IsQueryStorePro : False BypassPrepare : False

Parameter Mapping Properties

None

ResultSet

ResultName Variable Name

0 User:PONUMBER

1 User:StoreName

2 User:StoreEmail

3 User:Supplier

4 User:DeliveryDate

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

I then changed my query to the following as the above was showing conversion error in SSMS, when I try to include the below in Execute SQL Task the query isn't saved , any reason?

SELECT    DISTINCT  BizTalk_POA_HEADER.PONUMBER,FAN_Suppliers.SupplierName,  FAN_Company_Details.CompanyName, 
FAN_Company_Details.[PrimaryEmail], BizTalk_POA_HEADER.[DeliveryDate]
FROM         BizTalk_POA_HEADER INNER JOIN
                  FAN_PO_Details ON CAST(BizTalk_POA_HEADER.PONUMBER AS VARCHAR(128))  = CAST(FAN_PO_Details.PoNumber AS VARCHAR(128)) INNER JOIN
                  FAN_PO ON FAN_PO_Details.PurchaseOrderID = FAN_PO.PurchaseOrderID INNER JOIN
                  FAN_SupplierDetails ON FAN_PO.SupplierDetailsID = FAN_SupplierDetails.SuppliersDetailsID INNER JOIN
                  FAN_Suppliers ON FAN_SupplierDetails.SupplierID = FAN_Suppliers.SupplierID INNER JOIN
                  FAN_Company_Details ON FAN_PO.CompanyID = FAN_Company_Details.CompanyDetailsID

Thanks in Advance.

And does that query return a result set in ssms? How many rows are returned? also only tag which ssis version you are using not both 2008 & 2012. — Matt
Hi Matt, I am not sure about SSMS. The rows returned are 0 basically. I am using SSIS 2008. — Shivang
What should this task do if no records returned - nothing? What if 2 records are returned? You could for example use a SQL Task beforehand to count the records, and based on the count do some different workflow. — Nick.McDermaid
the problem is your query is returning 0 results to fix it change your query so it doesn't return 0 results. You have something wrong either in a join condition, or a table not meeting your criteria or??? we cannot fix it that is why we are suggesting you look at SSMS and try to correct your query to determine the correct results and query then update your package with it. — Matt
Hi Matt, I have modified the query in my original post, it isn't getting saved in Exceute SQL Task, any idea? — Shivang

btberry btberry · Accepted Answer · 2016-08-24T01:06:26

If 0 records are returned from your query and you are trying to populate a result set, that is the error you will get. Change your query so it always returns a single result and the error will go away.

SSIS Execute SQL Task error

1 Answers