Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

fail component error notification

I have created a package which forms data from flat file to sql database.I have a data flow where all my data is processed to destination.if the package fails by any chance i want an email notification with the cause of the error.how can i do this.please let me know..I'd start with Send Mail task and the OnError event handler for the package.|||Will i be able to send mail with the list of errors occured if the do the above|||Can you specify your goal in more detail? Do you want one email with all errors listed? Do you want all errors, or just the primary error that caused the package to fail?|||I want all the errors in one email.|||

This post might help:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

I created an example that uses a Script task in the OnError event to collect all the errors, and then uses a second Script task in the OnPostExecute event to process them. You could simply add a Send Mail task to the OnPostExecute to send the email.

Facxt Table Example

I need an example that shows how to uses multiple lookups to populate a fact table. The flow goes lilke this...

1. Read a staging table source that has source keys

2. for each source key, perform a lookup on the dimension table and return the surrogate key

3. Insert rows into the fact table with the surrogate keys

This is a standard approach that I've done many times in other ETL tools. However, I can't find any examples on how to get it to work. I have tried stringing the lookups together sequentially and using a multicast to peform the lookups in parallel. Neither approach would work. I could not find any examples on the web or in the SQL2005 samples.

Thanks,

Chris Busch

Blueprint Database

cbusch@.blueprintdatabase.com

Take a look at the samples you can download here:

http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm

This book is what I'd categorize as a "must have" if you're going to be spending much time with the MS BI stack.