locked
how to call a stored procedure in SSIS RRS feed

  • 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 Snippet

    CREATE 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)
       
        RETURN







    Friday, 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 Snippet

    CREATE 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)
       
        RETURN







    Friday, 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 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 

     

     

     

     

    • 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 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  @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.

    cheers


    Friday, 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 advice

     

    Wednesday, 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 off
    Thursday, 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 ON

    SET


    kkkk
    FMTONLY OFF




    Friday, 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


    kkkk
    Friday, 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,
    Suresh
    Friday, 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.aspx

    I 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 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'%

    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