Post reply

Table Valued Function in SSIS

  • August 26, 2008 at 7:16 pm

    #123205

    Hi

    I have a table values function and i would like to use the function in SSIS package OLEDB Souce

    select * from [dbo].[ResultSet] ?

    I have a input parameter... in parameters i could not give variable

    I could not Press OK button

    Error at Data Flow Task [OLE DB Source 1 [2932]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error

    can you lease help me

  • August 26, 2008 at 7:24 pm

    #863468

    not enough information I think...Can you post the full CREATE FUNCTION statement you are using?

    I'm guessing from what you posted the "parameter" you are trying to pass is a tablename...

    the only way to do something like "SELECT * FROM @TABLENAME" is with dynamic sql...since a function cannot use dynamic sql, you'll have to use a prcedure (if that is what you are trying to do.

    you can use a parameter for a value, like WHERE NM=@PARAM, but not to relace an object, like a table or column name.

    chinni (8/26/2008)

    Hi

    I have a table values function and i would like to use the function in SSIS package OLEDB Souce

    select * from [dbo].[ResultSet] ?

    I have a input parameter... in parameters i could not give variable

    I could not Press OK button

    Error at Data Flow Task [OLE DB Source 1 [2932]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

    An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error

    can you lease help me

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 26, 2008 at 7:28 pm

    #863470

    Create function [dbo].[ResultSet]

    (

    @ID int

    )

    returns @TempResult table (a,b,c,d)

    as

    Begin

    insert @tempresult

    select @a,@b,@c ,@d

    end

    return

    end

    this is the structure of the function

    In OLEDB source i used like

    select * from [dbo].[ResultSet] ?

  • August 27, 2008 at 6:27 am

    #863667

    Hi,

    If you want to use a table function as a source in SSIS then you will need to use a SQL command from a variable.

    Use an expression to derive the SQL command at runtime.

    e.g. "SELECT * FROM dbo.fn_MyFunction @MyParameter = " + (DT_WSTR,20)@MySSISVariable

    HTH

    Kindest Regards,

    Frank Bazan

  • August 27, 2008 at 9:18 am

    #863808

    thanks a lot

  • December 3, 2014 at 7:18 am

    #1762374

    You need to add braces, no space between bracket and brace:

    select * from [dbo].[ResultSet](?)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply