Optional variable/parameter in SSIS

Question

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!

Answer 1

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.

Answer 2

Heh... It was really easy.

All what I need is change query to:

select * from table1 where DateCol <= @DateVar OR @DateVar = ''

• RLF: A NULL and a zero-length string ('') are not the same thing.
• whitebeast: I understand. But I can't just enter NULL in variable expression. Now I can enter date or leave expression empty. I think I should edit main message...