Using Table-Valued Function for SSIS Lookup

Table-Valued function is a user defined function that returns a table data type. It can be used where table or view expressions are allowed in T-SQL queries. While views are limited to a single SELECT statement, the Table-Valued user defined function can contains additional statements that allow more powerful logic than in views. Unlike the stored procedures, It provides the row set based meta data which could be easily parsed in SSIS OLE DB source component.

Here is how to use it for lookup component

• Create a Table-Valued Function as below

CREATE FUNCTION [dbo].[udf_MyFunction] 

(    

    @Parameter NVARCHAR(30)  

)

RETURNS 

    @MyTable TABLE (

        [Code]              NVARCHAR (30)   NULL,

        [Column1]           NVARCHAR (50)   NULL,

        [Column2]           NVARCHAR (50)   NULL

      )

AS

BEGIN    

    INSERT  INTO @MyTable

    SELECT    @Parameter, Name, Color

    FROM dbo.MyTable

RETURN 

END



• In the SSIS Lookup component, specify the OLE DB connection and use SQL query result as

SELECT * FROM dbo.udf_MyFunction (N’1’)

• To pass in parameter at runtime, an expression to derive the SQL command could be used for the Lookup component as

"SELECT * FROM dbo.udf_MyFunction (" + (DT_WSTR, 30) @[User::MyVariable] + ")"