Wednesday, September 22, 2010

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] + ")"


image