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

 

Answer 1

Just create a new instance of "OLE DB Command".

Pick and choose your connection, and call the command as exec <procedure name>

Answer 2

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

Answer 3

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

Answer 4

jaegd - thanks!!!

Answer 5

It does not work If there are more than one rows are coming out of stored procedure.

Answer 6

I got  it solved on my end by replacing all temporary tables by temporary variables.
 

Answer 7

Please give an example of what doesn't work for you.

Answer 8

In the above post it should be table variable not temporary variable.

Conclusion:
It was table variable that Used instead of temporary table.

Answer 9

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.

Answer 10

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 

 

 

 

 

Answer 11

Hi Thank you very much..

It is now working fine for the changes you suggested..

Answer 12

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?

Answer 13

Post a sproc, or usage of the above sproc, which demonstrates the problem (e.g. OPENQUERY, EXEC, INSERT EXEC, so on...)

Answer 14

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

Answer 15

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?

 

Answer 16

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)

Answer 17

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

 

Answer 18

Try putting a non-executing SELECT statement at the beginning of the procedure, as jaegd showed earlier in this thread.

 

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

Hi jaegd,

Thanks a lot.  I have defined your suggestions in my stored procedure and my package is  working fine.

Regards,
Suresh

Answer 24

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.

Answer 25

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.

Answer 26

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

Answer 27

Thank you! You've just saved me at least 1/2 day of research!

Answer 28

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