SSIS: Passing parameter in OLE DB Source Editor to a table-valued function

This is a little bit of a workaround but it works pretty well. What we do is create a stored procedure called SetVar that will take an input parameter and assign it to a declared out parameter.

Create a procedure called SetVar in the OLE DB source database. It assigns @inputVar to @outVar

CREATE PROC SetVarInt
    @inputVar INT ,
    @outVar INT OUTPUT
AS
BEGIN
    SELECT @outVar = @inputVar;
END

In the SSIS OLE DB source use this SQL:

DECLARE @param1 INT;

EXEC [dbo].[SetVarInt] @inputVar = ?, -- int
    @outVar = @param1 OUTPUT -- int

SELECT * FROM [dbo].[GetByKey](@param1)

If nothing happens, the optimizer may have optimized the task away (check package execution log). In this case I will add the following to the end of the OLE DB source SQL, and connect this output to a RowCount task into a dummy variable.

SELECT 1 Done;

If you want to keep it simple, and just have the query in the OLDDB source without using other tricks, you can achieve that by writing the entire T-SQL code needed to get it done e.g.

declare @var1 as int = ?;
declare @var2 as datetime = ?;

select * 
from [dbo].[udf_test](@var1, @var2);

Doing it like that you do not need to have a separate task in SSIS to build the query and assign the parameteres, it all stays in the same place, and therefore easier to write, and even more important easier to understand by the person who will need to support this after you.