0

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!

2 Answers 2

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.

0

Heh... It was really easy.

All what I need is change query to:

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

2
  • A NULL and a zero-length string ('') are not the same thing.
    – RLF
    Feb 25, 2016 at 14:11
  • 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...
    – whitebeast
    Feb 25, 2016 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.