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