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