SSIS Talk

Wed 10 Oct 2007

SSIS - Stored Procedures and the OLE DB Source

Posted by Phil Brammer under SSIS Data flow

Many users have issues when trying to use a stored procedure in an OLE DB Source component. Here are some tips:

Ensure that there is a final select statement at the end of the stored procedure to ensure the correct resultset is returned
At the top of the stored procedure as part of the procedure code, add the following line: SET NOCOUNT ON
At the top of the OLE DB Source SQL statement and before you issue the EXEC YourSproc line, add the following: SET FMTONLY OFF.

This should ensure that when you click on the column mapping tab, the columns are correctly displayed.

Also another approach is to use a table variable in the stored proc. Then at the end, select from that table variable.

Paul Ibison Says:
November 15th, 2007 at 4:20 pm
Phil,
this is the way I always worked around the problem however it was recently pointed out to me that there are undesirable repurcessions of this workaround - namely your stored proc will get executed 5 times when the package executes. It might be that a temp table is needed for additional indexes and due to the large size of data etc but if at all possible I’d recommend using a table variable instead along with “SET NOCOUNT ON” in which case the stored proc will get executed once only.
Cheers,
Paul Ibison
Sachit Says:
February 27th, 2008 at 8:08 am
My SSIS package is getting name of stored procedure from a variable. When I prefix EXEC command in variable with SET FMTONLY OFF or put SET FMTONLY OFF in my stored procedure before SET NOCOUNT ON, my SSIS package gets hanged into some internal processing and never comes back. In such a case I need to press Ctl+Alt+Del to terminate it abnormally and restart it. Could you please suggest what could be the problem here?
Phil Brammer Says:
February 27th, 2008 at 8:52 am
Sachit,
Please post your question over on the SSIS Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1

There are several resources who can help you. Also, we’ll need to know more about your scenario. Where are you using this variable? (What component or task?)
maverick Says:
September 24th, 2008 at 3:10 pm
Thanx……….