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)  
        @MyTable TABLE (
            [Code]              NVARCHAR (30)   NULL,
            [Column1]           NVARCHAR (50)   NULL,
            [Column2]           NVARCHAR (50)   NULL
        INSERT  INTO @MyTable
        SELECT    @Parameter, Name, Color
        FROM dbo.MyTable

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



  1. Nice. I was wondering how SSIS would find a way to make this obscure. Thanks for clarifying.

  2. Nice post, i have scoured the net and you're the only one that is closest to the solution that i'm looking for. I pose you this question, if I have multiple parameters to the UDF that i'm calling how would the format go, what you posted above works great if the UDF has one paramater, but i'm not sure of the syntax of it for multiple parameters...thanks in advance for your help...