May 24, 2013 at 7:56 am
Hello,
We have an SP which is developed to give us two result sets based on selection. I want to use that SP in SSIS package. This package just copies the results from SP and dumps it into a flat file.
When I use that SP it is defaulting to first result set in OLE DB Source. So I created a new a SP which calls the first one to get the desired results. Not it shows me the correct fields which I can map to Flat File Destination.
But when I execute the package it is throwing me error and found online that sometimes when we use ‘SET NOCOUNT ON’ it may give this error.
Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider.
So instead of using the new SP, I declared a table variable and copied the results into it and thought of selecting it again through SQL command in OLEDB Source Editor. But when I do this it is not displaying me any results.
Inorder to see if anything wrong with SP, I manually inserted records into table variable but it still not showing any results. The package runs fine, but the text file does not have any data. Can anyone please let me know if I’m doing anything wrong?
First try with SP: It does not export any data to flat file.
DECLARE @Details TABLE
( ProductNumber varchar(10)
, CustomerNumber varchar(10)
)
INSERT INTO @Details
EXEC [dbo].[TestSP]
SELECT
ProductNumber
, CustomerNumber
FROM @Details
Just to see if it displays any results: It does not export any data to flat file.
DECLARE @Details TABLE
( ProductNumber varchar(10)
, CustomerNumber varchar(10)
)
INSERT INTO @Details
VALUES ('12345','67890')
SELECT
ProductNumber
, CustomerNumber
FROM @Details
Finally this one export data into flat file:
SELECT
'12345' AS ProductNumber
,'67890' AS CustomerNumber
May 26, 2013 at 1:50 pm
As far as I know, single procs which can return different result sets are not supported until SSIS 2012, where you can use the WITH RESULT SETS hint when calling the proc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 28, 2013 at 10:37 am
I set nocount on and it is displaying the results. Before it used to not export any data. I sed the below script in my ssis package and it is running fine.
SET NOCOUNT ON;
DECLARE @Details TABLE
( ProductNumber varchar(10)
, CustomerNumber varchar(10)
)
INSERT INTO @Details
EXEC [dbo].[TestSP]
SELECT
ProductNumber
, CustomerNumber
FROM @Details
May 28, 2013 at 2:20 pm
Shree-903371 (5/28/2013)
I set nocount on and it is displaying the results. Before it used to not export any data. I sed the below script in my ssis package and it is running fine.SET NOCOUNT ON;
DECLARE @Details TABLE
( ProductNumber varchar(10)
, CustomerNumber varchar(10)
)
INSERT INTO @Details
EXEC [dbo].[TestSP]
SELECT
ProductNumber
, CustomerNumber
FROM @Details
Where did the multiple result sets go? I see only one.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 29, 2013 at 9:40 am
In the SP. In the original SP based on parameter selection it gives two different results set i.e. one for SSRS and other for SSIS. I thought when i hardcode the parameter value in SSIS it gives me the second result set.
For Example: EXEC spTest @Output = 'SSIS'
But it is was giving me the first result set only. So I created a table variable and inserted the results into it and selected them again. It was giving the desired result set but it was not giving me any data. Once i add the SET NOCOUNT ON, it is displaying the data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply