Optional variable/parameter in SSIS I have a SSIS package which has the following:


Data Flow Task


OLE DB Source (select * from table1 where DateCol &lt;= ISNULL(@DateVar,DateCol ))
OLE DB Destination

DateVar variable


And I need to have a possibility to set value for DateVar or leave it empty or NULL (by default).
So, it could be two cases: DateVar have a value and DateVar have no value

Is it possible in SSIS? 

If so, how can I do this? 

Thank you in advance!
You may be interested in a standard approach to solve this problem. It is something called short circuit. To illustrate it, here's the code:

DECLARE @chooseDate datetime2
--SET @chooseDate = '1966-04-08'
SET @chooseDate = NULL
SELECT *
FROM DimCustomer
WHERE @chooseDate IS NULL OR BirthDate = @chooseDate


This way you can explicitly set variable to NULL, do not set variable to anything, or specify a value. 

The reason it is called short circuit is that based on the implementation of ANSI SQL this line of code:

WHERE @chooseDate IS NULL OR BirthDate = @chooseDate


has a possibility to evaluate only the first part of the expression (the IS NULL part) thus excluding the need to evaluate the second part, which makes the code slightly faster.