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!

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.

| improve this answer | |
0

Heh... It was really easy.

All what I need is change query to:

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

| improve this answer | |
  • A NULL and a zero-length string ('') are not the same thing. – RLF Feb 25 '16 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 '16 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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