Passing query parameters into an SSIS

Question

I have an SSIS package that queries data from a view using an SQL Command.

The SQL Command looks something like this:

SELECT from myView WHERE dateEntered >= GetDate() AND dateEntered < GetDate() + 1

Now I want to be able to specify the start and end dates such that the query behaves as:

SELECT from myView WHERE dateEntered >= startDate AND dateEntered < endDate

However, if start and end dates are not provided I need to use today's date and today's date + 1

What would be the best way to do this in an SSIS Package?

I can't change the view so it has to be done off the view or using variables in the SSIS. I'm not too familiar with SSIS and not sure if there is something in the toolbox that would let me process a date variable and then use it in the SQL Command?

Answer 1

I ended up passing in parameters to dtexec using /SET.

Then I used a Script Task to process the date in order to take care of invalid or other cases.

Answer 2

Why don't you use an ISNULL check for the dates? Like,

SELECT *
FROM myView 
WHERE dateEntered >= ISNULL(startDate,GETDATE()) 
    AND dateEntered < ISNULL(endDate,DATEADD(1,DAY,GETDATE()))

Then you can pass the start and end dates as parameters to the query from SSIS.

SELECT *
FROM myView 
WHERE dateEntered >= ISNULL(?,GETDATE()) 
    AND dateEntered < ISNULL(?,DATEADD(1,DAY,GETDATE()))

You can specify these parameters at the Execute SQL task's parameters section.