Search code examples
ssisssis-2012

Load data for yesterday's date and specific date

I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamivally. Please guide me to achieve this as I am new to SSIS.

Thanks in advance

Solution

  • Add a parameter to Package ParamDate. This parameter has to be manually provided a value(e.g. 03-21-2015). Leave it blank(NULL value) if yesterday's date has to be considered.

    Now define a variable inside your package VarDate

    Have an expression for VarDate like this -

    ISNULL(@[$Project::ParamDate])?DATEADD("day", -1, getdate()):@[$Project::ParamDate]

    I am assuming you are loading data in a DFT.

    So in the source query for that, you just need to add an extra condition in the where clause

    SELECT ...... FROM SomeTable WHERE LoadDate = ?

    In the "Parameters..." section of the source, assign the variable VarDate to the 0. For further details on how to map parameters see here.

    Hope it helps.

    EDIT As this was tagged under SSIS 2012,my solution involved project parameter, which is a feature under project deployment model. Package configurations are on the hand under package deployment model.

    If you want to use SQL Server package configuration table, then you can follow the steps below:

    Lets say the table's name is SSISConfiguration

    1. Have an Execute SQL task where you read this date from this table. 

      SELECT ISNULL(TstampUpdateDate, DATEADD(dd, -1, GETDATE()) FROM SSISConfiguration

    This script will fetch the value of TstampUpdateDate from the SSISConfiguration table and if it is NULL(set below), it will return yesterday's date.

    1. Map the result set to the variable @VarDate.

    2. Have one more Execute SQL task after this step which will update this field to NULL.

    3. Rest of the steps will be same as above(starting from the DFT bit).

    4. Next time the package runs, if the date field is not updated, it will query as per yesterday's date.