Sql-server – Optional variable/parameter in SSIS

sql serverssis

I have a SSIS package which has the following:

  1. Data Flow Task

    • OLE DB Source (select * from table1 where DateCol <= ISNULL(@DateVar,DateCol ))
    • OLE DB Destination
  2. 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!

Best Answer

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.

Related Question