Answered by:
how to call a stored procedure in SSIS

Question
-
I have to transfer data from source to destination using stored procedures result set. There might be some more transformation needed to store the final result in the destination table.
Appreciate an early feedback.
Qadir Syed
Friday, July 21, 2006 5:35 PM
Answers
-
Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code SnippetCREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @x char(1)
SET @x = (SELECT '1')
SELECT cast(@x as smallint)
RETURNFriday, May 11, 2007 9:59 AM
All replies
-
Just create a new instance of "OLE DB Command".
Pick and choose your connection, and call the command as exec <procedure name>
Friday, July 21, 2006 5:44 PM -
"Oledb command" Source executes stored procedures.But it does not recognize the output of the stored procedure.
If I have a select statement at the end of the Stored procedure that returns me some columns,then those columns are not recognized by "OLEDB Command" as out put columns.
Is there any advice for executing such stored procedure?Friday, May 11, 2007 8:04 AM -
Use a no-op select statement to "declare" metadata to the pipeline. Since stored procedures don't publish rowset meta-data like tables,views and table-valued functions, the first select statement of a stored procedure is used by the SQLClient OLEDB provider to determine column metadata.
Code SnippetCREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
SELECT CAST(1 as smallint) as Fake
-- Publish metadata
END
-- do real work starting here
DECLARE @x char(1)
SET @x = (SELECT '1')
SELECT cast(@x as smallint)
RETURNFriday, May 11, 2007 9:59 AM -
jaegd - thanks!!!Monday, May 28, 2007 9:25 PM
-
It does not work If there are more than one rows are coming out of stored procedure.Thursday, June 28, 2007 8:34 AM
-
I got it solved on my end by replacing all temporary tables by temporary variables.
Thursday, June 28, 2007 8:37 AM -
Please give an example of what doesn't work for you.
Thursday, June 28, 2007 8:50 AM -
In the above post it should be table variable not temporary variable.
Conclusion:
It was table variable that Used instead of temporary table.Friday, June 29, 2007 9:13 AM -
Hey try with the example below
CREATE PROCEDURE dbo.GenMetadata
AS
SET NOCOUNT ON
CREATE TABLE #test(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
) ON [PRIMARY]
INSERT INTO #test
SELECT '1','A','Z' union all select '2','b','y'
select id,name,SirName
from #test
drop table #test
RETURN
Please let me know the result.Friday, June 29, 2007 9:51 AM -
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code SnippetIF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A',
'Z'
UNION ALL
SELECT '2',
'b',
'y'
SELECT id,
[Name],
SirName
FROM #test
DROP TABLE #test
RETURN
GO
- Proposed as answer by Suresh Gudur Friday, February 26, 2010 11:39 AM
Friday, June 29, 2007 2:59 PM -
Hi Thank you very much..
It is now working fine for the changes you suggested..Thursday, July 12, 2007 1:48 PM -
Hello,
With the procedure you have told,SSIS package able to detect output of the stored procedure.
But there another problem introduced bcz of this procedure.Where According to your procedure the SP returns two datasets.
First dataset having 1 row and this is as a result of First select statement.
Second dataset bcz of our actual select query.
So SSIS chooses first dataset,So returns only one row having Null values for all columns.
How to overcome from this?Tuesday, August 14, 2007 8:50 AM -
Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
Wednesday, August 15, 2007 3:32 AM -
what if you want to execute a dynamic script i.e.
Code Blockcreate procedure etl.executeScript @scriptId int, @sessionId varchar(50)
as
set nocount on
declare @script nvarchar(max)
select @script=replace(script,'SESSION-ID',@sessionId)
from etl.script
where id=@scriptId
exec sp_executesql @script
return
exec etl.executeScript 1, 'my session'
basically every script has an id, and a sessionid. ole db command can't pick the meta data coming out of this stored procedure.
in the etl.script table, usually scripts meta data don't change, mostly it's the where class that changes.
when executing the ssis package, script id remains the same, the only thing that change is the sessionid. so for the engine meta data never changes, only the body does. for example a tipical script would look like
select fundid, fundname, descrptin
from dbo.currentsession
where sessionid='SESSION-ID'
so the etl.executeScript stroed procedure replace the sessionid with the passesed sessionid.
any help as to how i can get ole db source to show up the meta data in the columns section.
cheersFriday, November 16, 2007 4:09 AM -
I realize this probably isn't the answer you are looking for, but why not just put in the actual SQL, with a parameter for SessionID? What's the point of using a dynamic script if the metadata will always be the same?Sunday, November 18, 2007 10:30 PM
-
hi jewlch.
basically i'm trying to avoide opening up SSIS for every scrpt change. i only want to open SSIS if there's a meta data change to fix it up. also as you probably know there's a sql command text limit of 4000 characters. sometimes my queries get a little big, (trust me they are optimised to the best extent, but some of them have derived queries with in, so no point in breaking up really)Wednesday, November 21, 2007 3:00 AM -
Hey I am facing another problem that I have multiple #tables and multiple select statement in Store proce and SSIS says # table does not exist. As suggested by him if I put Return any where in between it will come out without executing multiple record set. Please adviceWednesday, June 11, 2008 10:37 PM
-
Try putting a non-executing SELECT statement at the beginning of the procedure, as jaegd showed earlier in this thread.Friday, June 13, 2008 9:07 PM
-
I've been having similar issues which jaegd's example looked like it would solve. It worked when I tested with a SQL server simple example of a proc using a temp table but failed for a Sybase data source with a more complex proc.
I cannot get this workaround to work with Sybase as the OLEDB source, even with a very simple proc example?
Are there additional complications with using Sybase?
Many thanks,
Chris
Thursday, June 19, 2008 5:44 PM -
Hi I still can't see the outpout columns in OleDb Command Output Columns
, how can i get the output columns. I want to use them to insert in ole db destination
here is my sp:
create PROCEDURE [dbo].[GetData] (
@user varchar(50)
) AS
set nocount on
-- Publish metadata for ssis
if 1=0
begin
select '' x, '' y, '' z
end
declare @user_tmp table
(
x varchar(max),
y varchar(max),
z varchar(max)
)
insert into @user_tmp
select 'x1' x, 'y1' y, 'z1' z
select distinct * from @user_tmp
set nocount offThursday, March 19, 2009 3:38 PM -
i had the same problem--
there is two things u need to do . u need to alter your stored procedure and add is just after the begin statment.
SET NOCOUNT ONSET
kkkkFMTONLY OFFFriday, April 3, 2009 4:31 PM -
Here is what u need to do alter your stored procdures and just after the
Begin statement do this
SET NOCOUNT ON
SET FMTONLY OFF
kkkkFriday, April 3, 2009 4:34 PM -
Hi jaegd,
Thanks a lot. I have defined your suggestions in my stored procedure and my package is working fine.
Regards,
SureshFriday, February 26, 2010 11:38 AM -
Hello All,
I have a stored procedure which returns a a lot of rows . I need help in executing the stored procedure and saving the output to a flat file destination. Please let me know how to do this.
Wednesday, May 19, 2010 11:34 PM -
Here is what u need to do alter your stored procdures and just after the
Begin statement do this
SET NOCOUNT ON
SET FMTONLY OFF
kkkk
I read at multiple places by using the "SET FMTONLY OFF" will cause it to execute 5 times. Here's an example reference. http://ssisblog.replicationanswers.com/2007/11/13/coping-with-no-column-names-in-the-oledb-data-source-editor.aspxI recall there's an actual blog or forum post describing this. However, I'm unable to find it anymore.
Tuesday, July 13, 2010 12:10 AM -
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline.
Code SnippetIF OBJECT_ID('[dbo].[GenMetadata]', 'P') IS NOT NULL
DROP PROCEDURE [dbo].[GenMetadata]
GO
CREATE PROCEDURE [dbo].[GenMetadata]
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
-- Publish metadata
SELECT CAST(NULL AS INT) AS id,
CAST(NULL AS NCHAR(10)) AS [Name],
CAST(NULL AS NCHAR(10)) AS SirName
END
-- Do real work starting here
CREATE TABLE #test
(
[id] [int] NULL,
[Name] [nchar](10) NULL,
[SirName] [nchar](10) NULL
)
INSERT INTO #test
SELECT '1',
'A'%
Saturday, February 19, 2011 7:13 PM -
Thank you! You've just saved me at least 1/2 day of research!Friday, October 28, 2011 12:27 PM
-
There is a real easy way to deal with this .. Use CTE to replace the temp table .. Works like a charm .. No need to deal with FMTONLY ON/OFF anymore ..Friday, November 6, 2015 3:34 PM