Please help fix this error in SSIS 2008 package, data flow..

Question

Please help fix this error in SSIS 2008 package, data flow..

VoldemarG

Hall of Fame

Points: 3883
More actions
February 25, 2016 at 8:10 pm

#399775

i am executing the attached script (SSIS 2008 r2)
SCRIPT1.PDF either directly in OLEDB data source via query and outputting result as is to FLAT FILE.
OR
as a compiled SP:
SET FMTONLY OFF
exec usp_rptXXXDaily
both script and sp return the right result directly in SSMS.
also PREVIEW of OLEDB source is correct. (see the second attachment).
HOWEVER, when running package, attempting to output to Flat txt file,
(see the third attachment of FileConnectionManager)
I am getting this weird error:
[OLE DB Source [5463]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
WHAT CAN I DO? plz, help
Likes to play Chess

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

VoldemarG (2/25/2016)
i am executing the attached script (SSIS 2008 r2)
SCRIPT1.PDF either directly in OLEDB data source via query and outputting result as is to FLAT FILE.
OR
as a compiled SP:
SET FMTONLY OFF
exec usp_rptXXXDaily
both script and sp return the right result directly in SSMS.
also PREVIEW of OLEDB source is correct. (see the second attachment).
HOWEVER, when running package, attempting to output to Flat txt file,
(see the third attachment of FileConnectionManager)
I am getting this weird error:
[OLE DB Source [5463]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.
WHAT CAN I DO? plz, help

Does your stored procedure have SET NOCOUNT ON in it? If it doesn't, add right at the beginning. The row counts that are returned if that setting isn't available will sometimes be interpreted by GUIs as a result set screwing up the works for the real result set. SSIS is a GUI and susceptible to a similar fault.

It's also possible that SSIS isn't seeing your Temp Tables. You may have to convert them to Table Variables or just avoid temporary structures when writing to disk.

Also, doing an ORDER BY 1 is a deprecated feature and using WITH (NOLOCK) is a dangerous feature.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

SSIS won't work correctly with temp tables. You need to use a permanent table for your script.

Unless there's a workaround using certain settings, but I don't remember any.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Luis Cazares (2/26/2016)
SSIS won't work correctly with temp tables. You need to use a permanent table for your script.
Unless there's a workaround using certain settings, but I don't remember any.

Heh... EXCELLENT! Another reason for me to not use SSIS! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

On your Connection Manager just set the RetainSameConnection property to True so the same connection is used across all your database requests.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Jeff Moden (2/27/2016)
Luis Cazares (2/26/2016)
SSIS won't work correctly with temp tables. You need to use a permanent table for your script.
Unless there's a workaround using certain settings, but I don't remember any.
Heh... EXCELLENT! Another reason for me to not use SSIS! 😛

As if you needed another reason 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato