SSIS: Using stored procedures inside an OLE DB Source component

re: SSIS: Using stored procedures inside an OLE DB Source component

mf_fits — Wed, 20 Dec 2006 23:53:39 GMT

Hi Jamie,

would it not be helpfull to use "SET FMTONLY" before executing the select statement including the stored procedure?

As described here [http://msdn2.microsoft.com/en-us/library/ms173839.aspx] it forces to send the metadata.

We had to use this for some stored procedures, because we faced the same problem that no masterdata was returned as shown here:

http://www.fits-consulting.de/blog/content/binary/SET_FMTONLY.jpg

cheers,

Markus

re: SSIS: Using stored procedures inside an OLE DB Source component

jamie.thomson — Thu, 21 Dec 2006 09:39:24 GMT

Markus,

I've just tried it with the example test_proc shown above and it wouldn't even run in SSMS because I get the error:

"Invalid object name '#tmp'."

I'm happy to be proved wrong though if you can show me an example of it working with "SET FMTONLY" and then removing it and it still working at execute-time.

-Jamie

re: SSIS: Using stored procedures inside an OLE DB Source component

Scott — Wed, 24 Jan 2007 19:30:18 GMT

Thanks, you just saved me a big headache. This is great info.

re: SSIS: Using stored procedures inside an OLE DB Source component

Zoran Pecenovic — Thu, 25 Jan 2007 15:30:18 GMT

If you use a table variable instead of a temp table it actually works.

Not sure why though...

Try this

create procedure [dbo].[testPrepare]

declare @t table (a int);

insert into @t select 1;

select a from @t;

re: SSIS: Using stored procedures inside an OLE DB Source component

Louis — Wed, 09 May 2007 16:17:15 GMT

To solve the [same] pb under SQL Server 2005 / OLEDB client:

just add SET NOCOUNT ON at the begining of the procedure !

[ SET FMTONLY OFF and table variables don't solve it]

re: SSIS: Using stored procedures inside an OLE DB Source component

Scott A — Mon, 14 May 2007 20:39:54 GMT

When using this call with a parameter I cannot find a way for this to work.

SET FMTONLY OFF

EXEC [dbo].[spy_BlahBlah_All_Blah] 606,606

Works....

Passing in the parameters does NOT work.

SET FMTONLY OFF

EXEC [dbo].[spy_BlahBlah_All_Blah] ?,?

re: SSIS: Using stored procedures inside an OLE DB Source component

Scott A — Mon, 14 May 2007 20:45:17 GMT

And also as an added bonus this will not even compile in SSIS, when clicking the OK or preview button SSIS throws and error stating a non-specific error has orrured.

re: SSIS: Using stored procedures inside an OLE DB Source component

Iram — Fri, 01 Jun 2007 14:40:44 GMT

I'm trying to call oracle stored procedure from OleDB Command in SSIS but get an error:

An OLE DB error has occured. Error Code : 0x80040E51.

An OLE DB record is available. Source: OraOLEDB Hresult : 0x80040E51.

Description :"provider cannot provide parameter information and

SetParameterInfo has not been called."

Is someone can help with it?

Thanks

Iram.Levinger@comverse.com

re: SSIS: Using stored procedures inside an OLE DB Source component

RonaldL — Fri, 20 Jul 2007 11:25:14 GMT

Did some testing with the OLEDB Source in the Designer when using a stored procedure as the data source.

--Works in OLEDB Reader, gives back columns and preview

CREATE PROCEDURE csp_test

SET NOCOUNT ON

SELECT *

FROM sys.columns

-- Does not work at run time, only preview works

CREATE PROCEDURE csp_test

SET NOCOUNT ON

SELECT *

INTO #tmp_table

FROM sys.columns

SELECT * FROM #tmp_table

-- Does work! Works at runtime and design time

CREATE PROCEDURE csp_test

SET FMTONLY OFF

SET NOCOUNT ON

SELECT *

INTO #tmp_table

FROM sys.columns

SELECT * FROM #tmp_table

re: SSIS: Using stored procedures inside an OLE DB Source component

Panos — Wed, 01 Aug 2007 13:38:05 GMT

I can also confirm that:

SET FMTONLY OFF

SET NOCOUNT ON

does work within a stored proc! I didn't make any other changes just that.

Panos.

re: SSIS: Using stored procedures inside an OLE DB Source component

Tenny S — Fri, 03 Aug 2007 00:54:52 GMT

ditto Panos' comments..

I have these 2 settings and it worked.

SET FMTONLY OFF

SET NOCOUNT ON

re: SSIS: Using stored procedures inside an OLE DB Source component

Luke — Sat, 11 Aug 2007 21:42:31 GMT

If the function returns more than a tiny amount of data (starting at about 200 rows or so), the table variable will cause SEVERE performance degradation. Table variables, to the best of my understanding, have to be "promoted" at this size (from the heap into the database itself, maybe?) and SQL Server doesn't handle this well at all.

re: SSIS: Using stored procedures inside an OLE DB Source component

Todd — Mon, 20 Aug 2007 18:22:24 GMT

I'm trying to truncate an oracle table. I'm using an OLE DB Command and I get the same error. How can I get around this??

re: SSIS: Using stored procedures inside an OLE DB Source component

Manuel — Mon, 17 Sep 2007 13:39:44 GMT

Hello:

I come to this situation of cannot executing a stored Procedure from within an OLE DB Source. I created the function but the issue I have now is that I want to pass a parameter to it like this:

SELECT col1

FROM dbo.test_func(?) AS test_func_1

When I click the parameters button says the following message:

"Parameters cannot be xtracted from the SQL Command. The Provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable."

I created the function to receive a datetime parameter in it.

Any insights will be greatly appreciated.

Attn Manuel

re: SSIS: Using stored procedures inside an OLE DB Source component

Holly — Mon, 22 Oct 2007 12:38:51 GMT

Thanks to all for this thread. Whenever I find a sticky problem, I will usually find a solution on this blog, from Jamie and the commenters who expand on it.

Holly

re: SSIS: Using stored procedures inside an OLE DB Source component

sharon — Tue, 23 Oct 2007 20:48:54 GMT

I tried in the SQL window:

SET FMTONLY on

Select top 1 from micro_tbl

SET FMTONLY off

exec dbo.my_micro_procedure

It did the metadata okay and created my flat file okay. Would not execute the procedure.

Tried these statements at the beginning of my stored procedure also. Again did the metadata okay and created the flat file okay. Did not execute the rest of the procedure.

Any one got any ideas?

re: SSIS: Using stored procedures inside an OLE DB Source component

sharon — Thu, 01 Nov 2007 14:47:12 GMT

Well, I do not know why I can not follow EXACT directions. I finally did as advised and my procedure works.

In the Data Flow task:

SET FMTONLY OFF

execute my_procedure name

In procedure:

SET NOCOUNT ON

Thank you!!!!!!

re: SSIS: Using stored procedures inside an OLE DB Source component

ssis_rookie — Mon, 19 Nov 2007 08:02:40 GMT

Just encountered the same problem. I am converting DTS to SSIS ... and change connection from SQL 2000 to SQL 2005.

Here's the deal:

I used migration wizard to migrate the dts; the dataflow has an OLE DB Source running a parameterized Stored Procedure ... all is well and no error found after migration ... was able to compile, run, etc. OLE DB SOurce is using OLE DB Connection Manager to SQL 2000

So I changed it to our new server which is SQL 2005, and there it is ... "No value is given for one or more required parameters"!!! Same OLE DB Source, same sp, same parameter mapping ... just a change in server.

Any idea what's causing the problem??? Anyway ... temporarily I used SQL Command from Variable and build my query from there. That seems to do the trick but very ugly implementation.

re: SSIS: Using stored procedures inside an OLE DB Source component

Mark — Tue, 08 Jan 2008 14:08:13 GMT

Just another way to solve the "Stored procedures expose no explicit output contract". My intention was to avoid UDF. My colleague showed this trick to me. This seemed to work, i hope it might help someone

1 create procedure test_proc

2 as

3 begin

4 If 1 =2

5 Select Null As col1 , Null As Col2

6 --Table created at the top of the procedure

7 create table #tmp

8 (

9 col1 varchar(20),

10 col2 varchar(50)

11 )

13 --Put some values in

14 insert into #tmp

15 values ('abc','xyz')

17 --Only now do we return some data

18 Select col1,col2

19 From #tmp

20 END

re: SSIS: Using stored procedures inside an OLE DB Source component

jdieter — Thu, 17 Jan 2008 17:19:35 GMT

This doesn't work. It gets the meta data to the package, but when you execute the package you get [OLE DB Source [109]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

does ANYONE have proof that an SSIS package can get data from a stored procedure? My proc crosstabs data into a # table then selects from the table. Is there ANY way to get this to work?

re: SSIS: Using stored procedures inside an OLE DB Source component

Doug Tumeo — Mon, 04 Feb 2008 20:27:28 GMT

I just tried this, and it solved my problem. To be clear on what I've tried...

Created stored proc ps_GetWorkDates that "returns" a table variable that is populated with a series of dates. In order for this to work, I had to include SET NOCOUNT ON in the proc, as instructed above.

Then, created an SSIS package that "reads" the proc output and stages my data in the warehouse using an OLE DB Source with this SQL Command Text:

SET FMTONLY OFF

Execute ps_GetWorkDates

So in short, THANKS!

re: SSIS: Using stored procedures inside an OLE DB Source component

piyush — Mon, 11 Feb 2008 10:01:13 GMT

SFTP IN ASP.NET USING C#

re: SSIS: Using stored procedures inside an OLE DB Source component

Tuiyan Gulab — Wed, 27 Feb 2008 13:41:32 GMT

In my case, SSIS package is coming through a variable. When I set SET FMTONLY OFF withing stored procedure along with SET NOCOUNT ON, BID Studio goes for everlasting internal processing and then I need to press Ctl + Alt + Del to abnormally terminate it and restart it. Any suggesstions?

re: SSIS: Using stored procedures inside an OLE DB Source component

Sachit — Thu, 28 Feb 2008 07:12:27 GMT

My stored procedure is having a temp table and it is taking around 9 mins to execute. Now when I set it as a data source to SSIS package, it seems SSIS package starts executing the stored proc at design time only to fetch the metadata and because stored procedure is taking 9 mins so drag and drop in SSIS also hangs up.

Is it the same problem you are facing Tuyan?

re: SSIS: Using stored procedures inside an OLE DB Source component

JENNIFER — Wed, 09 Apr 2008 22:24:45 GMT

Using the FMTONLY OFF worked for me as well...but there are definately performance issues encountered when you do so. It seems to be running the proc once to get the metadata list and once then to retrieve your data. If your proc takes a long time to run...you are waiting for a while.

re: SSIS: Using stored procedures inside an OLE DB Source component

Conor Ryan — Wed, 07 May 2008 05:19:29 GMT

'SET NOCOUNT ON' worked for me. Thanks

re: SSIS: Using stored procedures inside an OLE DB Source component

FreeHansje — Mon, 28 Jul 2008 10:40:01 GMT

Tnx for the info, helped a lot.

re: SSIS: Using stored procedures inside an OLE DB Source component

Adam Machanic — Tue, 29 Jul 2008 17:28:07 GMT

I just fought SSIS for an hour trying to get it to read data from a certain stored procedure via an OLE DB source. Other stored procedures work fine, and I have no clue why this one in particular caused a runtime exception (the old "A rowset based on the SQL command was not returned by the OLE DB provider."). The stored procedure in question uses no temp tables or parameters -- it's just a single query. I tried both the SET NOCOUNT ON and SET FMTONLY OFF, to no avail. Finally, I flipped over to a SqlDataReader source, which fixed the problem. Only issue there is that I've run into a bunch of issues on other projects with the SqlDataReader source and generally try to avoid it. I really wish SSIS would just work the way we expect it to...

I wasn't able to try the TVF because there are some legacy constraints in this environment (mostly due to personnel mindset rather than actual technical problems). But thanks, Jamie, for the nod in this post; it's always fun to find one's own name while researching a problem :-)

re: SSIS: Using stored procedures inside an OLE DB Source component

Angel Abundez — Tue, 19 Aug 2008 19:43:27 GMT

Table variable worked for me. Thanks to Zoran for his comment!

re: SSIS: Using stored procedures inside an OLE DB Source component

Ian — Wed, 20 Aug 2008 16:47:59 GMT

After finding out the function would not work for me, I tried the SET FMTONLY OFF and SET NOCOUNT ON in my stored procedure, which worked, but like other people mentioned, Visual Studio seems to run the stored procedure during design time which slows EVERYTHING down. But I think I found a work around:

* I used a DataReaderSource, and set the SQLCommand to "SET FMTONLY OFF; EXEC myproc"

* I click on "Column Mappings" and then waited while it got the columns.

* I clicked back on to "Component Properties" and set ValidateExternalMetadata to FALSE.

So, it seems that setting ValidateExternalMetadata to FALSE will let Visual Studio save the current metadata and not check it every time you try to do something.

Also, I expected the package to take twice as long when I eventually ran it (run the procedure once to check the metadata and then again to actually get the data), but it ran just fine.

I'm not sure if the ValidateExternalMetadata works for the OLE DB Source, but it seems to be working for the DataReaderSource.

re: SSIS: Using stored procedures inside an OLE DB Source component

Chuck — Fri, 12 Sep 2008 21:46:51 GMT

For me, one of my procedure parameters was type xml. I couldn't get the columns to show up in the Ole Db Source for the life of me. After I changed the paramter type to varchar(max), I was able to see the column names. Unfortunately, I'm not at a point to see if the runtime works, yet.

Chuck

re: SSIS: Using stored procedures inside an OLE DB Source component

Henrik Krogh — Tue, 18 Nov 2008 01:15:58 GMT

The trick is to include a non-executed dummy select statement at the top of the SP as well as the "nocount on" option.

The dummy select statement "displays" metadata (or rather the OLE db uses the first encountered select statement to generate metadata) to the SSIS pipeline. It's important that it is a non-executed statement f.ex. in a IF 1=2 block (else SSIS will use the dummy select as the result and ignore further output).

you make sure that the metadata of the dummy result set is equal to the intended result set of the sp.

You need the nocount on option, because SSIS will get confused by the verbose output of Sps. (that yields this error:A rowset based on the SQL command was not returned by the OLE DB provider. )

When you do the above you can compose any sp of any complexity - as long as it delivers a single result set that matches the dummy select statement's metadata.

I believe that only using the no count option, requires that the first select statement has compatible metadata with the intended result set of the procedure - though havn't tjecked it.

re: SSIS: Using stored procedures inside an OLE DB Source component

Tue, 18 Nov 2008 13:13:15 GMT

THANKS SO MUCH!

SET FMTONLY OFF works a treat!