Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Using stored procedures inside an OLE DB Source component

Many people like to use stored procedures as the source of data for the SSIS pipeline. In theory there is nothing wrong with that however it does have its complications. Depending on the way the stored procedure is written, it may or may not be suitable to be used in this way. Namely, it is possible to preview the data being returned from a stored procedure but when switching to the 'Columns' tab of the OLE DB Source Adapter you do not see any columns. 

 

The reason is fairly simple. At design-time SSIS interrogates the OLE DB Provider that sits between itself and the source of data and requests the metadata of the data that would be returned from the query. It is the job of the OLE DB Provider to interrogate the underlying source and pass the metadata back to SSIS. However, stored procedures do not possess interogatable metadata - you can confirm this by executing:

select o.[name],c.*

from sys.columns c

inner join sys.objects o

on c.object_id = o.object_id

where o.[name] = '<your_stored_procedure_name>'

This presents a problem for the OLE DB Provider. It needs to know the metadata but it can't get it from the system catalog so what does it do? To all intents and purposes it has to make a "best guess" which essentially means it takes the metadata of a select statement at the top of the stored procedure. However, if no such query exists then it simply can't do it. Here is a stored procedure that exhibits this very problem:

 

    1 create procedure test_proc

    2 as

    3 begin

    4 

    5     --Table created at the top of the procedure

    6     create table #tmp

    7     (

    8         col1 varchar(20),

    9         col2 varchar(50)

   10     )

   11 

   12     --Put some values in

   13     insert into #tmp

   14     values    ('abc','xyz')

   15 

   16     --Only now do we return some data

   17     Select col1,col2

   18     From    #tmp

   19 END

 

Go ahead and create this stored procedure and try and use it in an OLE DB Source Adapter. You won't be able to.

 

At this point I want to refer you to an excellent blog post from Adam Machanic called "Stored procedures are not parameterized views". It explains in excellent detail about this inherent problem with stored procedures. As Adam puts it "Stored procedures expose no explicit output contract". That's another way of saying what I said above about stored procedures not possessing any metadata. If you read through to the end of Adam's article he states that table-valued user-defined-functions (UDFs) exist as a workaround to this problem. I took Adam at his word and changed the above stored procedure into a table-valued UDF. Here is that function - it returns exactly the same data as the stored procedure:

 

    1 create function test_func ()

    2 returns @tmp table --set up our table-valued UDF

    3     col1 varchar(20),

    4     col2 varchar(50)

    5 )

    6 as

    7 begin

    8     --Put some values into table

    9     insert into @tmp

   10     values    ('abc','xyz')

   11 

   12     --and return the data

   13     return

   14 end

Now execute this query again:

select o.[name],c.*

from sys.columns c

inner join sys.objects o

on c.object_id = o.object_id

where o.[name] = 'test_func'

Sure enough, the metadata (or 'contract' in Adam's terminology) is available for all to see and we can now use this function in our OLE DB Source adapter using the following query:

select col1, col2 from test_func ()

without any problems.

 

This problem seems to have vexed many SSIS developers in the past and I've been meaning to write this explanation for ages now - I just never got round to it.

 

-Jamie

Published Wednesday, December 20, 2006 1:24 PM by jamie.thomson

Comments

 

mf_fits said:

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

December 20, 2006 11:53 PM
 

jamie.thomson said:

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

December 21, 2006 9:39 AM
 

Scott said:

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

January 24, 2007 7:30 PM
 

Zoran Pecenovic said:

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

Not sure why though...

Try this

create procedure [dbo].[testPrepare]

as

declare @t table (a int);

insert into @t select 1;

select a from @t;

go

January 25, 2007 3:30 PM
 

Louis said:

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]

May 9, 2007 5:17 PM
 

Scott A said:

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] ?,?

May 14, 2007 9:39 PM
 

Scott A said:

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.

May 14, 2007 9:45 PM
 

Iram said:

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

June 1, 2007 3:40 PM
 

RonaldL said:

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

AS

SET NOCOUNT ON

SELECT *

FROM sys.columns

GO

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

CREATE PROCEDURE csp_test

AS

SET NOCOUNT ON

SELECT *

INTO #tmp_table

FROM sys.columns

SELECT * FROM #tmp_table

GO

-- Does work! Works at runtime and design time

CREATE PROCEDURE csp_test

AS

SET FMTONLY OFF

SET NOCOUNT ON

SELECT *

INTO #tmp_table

FROM sys.columns

SELECT * FROM #tmp_table

GO

July 20, 2007 12:25 PM
 

Panos said:

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.

August 1, 2007 2:38 PM
 

Tenny S said:

ditto Panos' comments..

I have these 2 settings and it worked.

SET FMTONLY OFF

SET NOCOUNT ON

August 3, 2007 1:54 AM
 

Luke said:

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.

August 11, 2007 10:42 PM
 

Todd said:

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??

August 20, 2007 7:22 PM
 

Manuel said:

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

September 17, 2007 2:39 PM
 

Holly said:

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

October 22, 2007 1:38 PM
 

sharon said:

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?

October 23, 2007 9:48 PM
 

sharon said:

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!!!!!!

November 1, 2007 2:47 PM
 

ssis_rookie said:

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.

November 19, 2007 8:02 AM
 

Mark said:

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     )

  12

  13     --Put some values in

  14     insert into #tmp

  15     values    ('abc','xyz')

  16

  17     --Only now do we return some data

  18     Select col1,col2

  19     From    #tmp

  20 END

January 8, 2008 2:08 PM
 

jdieter said:

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?

January 17, 2008 5:19 PM
 

Doug Tumeo said:

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!

February 4, 2008 8:27 PM
 

piyush said:

SFTP IN ASP.NET USING C#

February 11, 2008 10:01 AM
 

Tuiyan Gulab said:

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?

February 27, 2008 1:41 PM
 

Sachit said:

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?

February 28, 2008 7:12 AM
 

JENNIFER said:

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.

April 9, 2008 11:24 PM
 

Conor Ryan said:

'SET NOCOUNT ON' worked for me. Thanks

May 7, 2008 6:19 AM
 

FreeHansje said:

Tnx for the info, helped a lot.

July 28, 2008 11:40 AM
 

Adam Machanic said:

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 :-)

July 29, 2008 6:28 PM
 

Angel Abundez said:

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

August 19, 2008 8:43 PM
 

Ian said:

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.

August 20, 2008 5:47 PM
 

Chuck said:

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

September 12, 2008 10:46 PM
 

Henrik Krogh said:

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.

November 18, 2008 1:15 AM
 

S said:

THANKS SO MUCH!

SET FMTONLY OFF works a treat!

November 18, 2008 1:13 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems