how to call a stored procedure in SSIS
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
July 21st, 2006 8:35pm
Just create a new instance of "OLE DB Command". Pick and choose your connection, and call the command as exec <procedure name>
July 21st, 2006 8:44pm
"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?
May 11th, 2007 11:04am
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 Snippet CREATE PROCEDURE dbo.GenMetadataAS 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) RETURN
May 11th, 2007 12:59pm
jaegd - thanks!!!
May 29th, 2007 12:25am
It does not work If there are more than one rows are coming out of stored procedure.
June 28th, 2007 11:34am
I got it solved on my end by replacing all temporary tables by temporary variables.
June 28th, 2007 11:37am
Please give an example of what doesn't work for you.
June 28th, 2007 11:50am
In the above post it should be table variable not temporary variable.Conclusion:It was table variable that Used instead of temporary table.
June 29th, 2007 12:13pm
Hey try with the example belowCREATE PROCEDURE dbo.GenMetadataASSET NOCOUNT ONCREATE TABLE #test([id] [int] NULL,[Name] [nchar](10) NULL,[SirName] [nchar](10) NULL) ON [PRIMARY]INSERT INTO #testSELECT '1','A','Z' union all select '2','b','y'select id,name,SirName from #testdrop table #testRETURNPlease let me know the result.
June 29th, 2007 12:51pm
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline. Code Snippet IF 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
June 29th, 2007 5:59pm
Hi Thank you very much..It is now working fine for the changes you suggested..
July 12th, 2007 4:48pm
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?
August 14th, 2007 11:50am
Posta sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)
August 15th, 2007 6:32am
what if you want to execute a dynamic script i.e. Code Block create 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 @scriptreturnexec 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 likeselect fundid, fundname, descrptinfrom dbo.currentsessionwhere 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.cheers
November 16th, 2007 7:09am
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?
November 19th, 2007 1:30am
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)
November 21st, 2007 6:00am
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 advice
June 12th, 2008 1:37am
Try putting a non-executing SELECT statement at the beginning of the procedure, as jaegd showed earlier in this thread.
June 14th, 2008 12:07am
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
June 19th, 2008 8:44pm
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 off
March 19th, 2009 6:38pm
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 ON SET kkkkFMTONLY OFF
April 3rd, 2009 7:31pm
Here is what u need to do alter your stored procdures and just after the Begin statement do this SET NOCOUNT ON SET FMTONLY OFFkkkk
April 3rd, 2009 7:34pm
Hi jaegd,Thanks a lot. I have defined your suggestions in my stored procedure and my package is working fine.Regards,Suresh
February 26th, 2010 2:38pm
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.
May 20th, 2010 2:34am
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.aspx I recall there's an actual blog or forum post describing this. However, I'm unable to find it anymore.
July 13th, 2010 3:10am
With a local temp table created in the stored procedure, use a no-op select statement to "declare" metadata to the pipeline. Code Snippet IF 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'%
February 19th, 2011 10:13pm
Thank you! You've just saved me at least 1/2 day of research!
October 28th, 2011 8:37am