0

I have build an ETL package with two sequence containers. One for a full load and one for a incremental load. enter image description here

I want the package to excecute the incremental load each "normal" day When it is a sunday to execute the full load

Do i need to use a script task for this?

billinkc
  • 59,250
  • 9
  • 102
  • 159
Robertjan Rensen
  • 1
  • 2
  • 1
    How about 2 separate packages. I assume this is deployed and run through jobs. Schedule the Full load for Sundays and the incremental load Mon-Sat. – KeithL Nov 21 '19 at 14:35

3 Answers3

2

There are a number of ways to accomplish this. One fairly straight forward way would be to add an Execute SQL Task ahead of your two Sequence Containers with some code to determine the day of the week.

This is pretty clear in it's intention:

SELECT 
  CASE
    WHEN DATENAME(WEEKDAY, GETDATE()) = 'Sunday' THEN 1
    ELSE 0
  END;

Set the result set to Single Row, and on the Result Set tab, assign the query output to a variable.

Create a Precedence Constraint from the task to the incremental container. Edit the constraint to be an Expression and Constraint and specify that @[User::MyVariable]==0.

Create another constraint to the full refresh container, but specify the variable value as 1.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
  • I was going to also suggest a similar approach inside SSIS with setting a variable in an expression task using @weekday = datepart("dw",getdate()) – KeithL Nov 21 '19 at 14:40
  • Yeah, but I shy away from `DATEPART` because of it's reliance on `DATEFIRST`. Lots of ways to get to where the OP wants to go, though. – Eric Brandt Nov 21 '19 at 14:42
  • 2
    OTOH, I also would've done this in two packages, @KeithL. – Eric Brandt Nov 21 '19 at 14:46
2

There are a few different ways to solve this.

First approach

The first way is through an Expression on each of your Sequence Containers. The Expressions will control the Disable state of the container and the expression you are looking for is DATEPART for a Weekday dw

Is it Sunday?

DATEPART("weekday", getdate()) == 1

Is it not Sunday?

DATEPART("weekday", getdate()) != 1

Next approach

The problem with using the getdate approach is testing - how are you going to test all the possible scenarios? Are you going to change the clock on the computer to verify the Sunday/nonSunday branch works?

I would create an SSIS Variable called CurrentDate of type DateTime and initialize it to today.

I would create a second SSIS Variable called IsSunday of type Boolean and set an expression on this variable to be DATEPART("weekday", @[User::CurrentDate] ) == 1

I would then modify my two Sequence Containers to have a new, simplified expression on their Disabled state

Is it Sunday @[User::IsSunday]

Is it not Sunday !@[User::IsSunday]

Run the package and verify the non-Sunday branch lights up (today is Thursday). Stop execution and then change the value of @[User::CurrentDate] to last Sunday (2019-11-17) and restart the package. Verify the Sunday branch lights up and then stop execution.

Now that you have the logic sound, the remaining step is to modify the SSIS Variable CurrentDate to have an expression on it of getdate(). Restart the package and verify the non-Sunday branch has once again lit up for execution.

Next, Next approach

Congratulations, your package is implemented and it runs well. Until it doesn't. The server maintenance window runs long and this package doesn't actually start until Monday morning at 12:15 a.m. How are the operators going to be able to tell the package "Run the Sunday logic?" You aren't, unless you edit the package and nobody wants to do that.

In this situation, I could use the basic construct I've built above but now, I would add a Parameter to the package, of type Boolean and call it something like ForceSunday and the default value is False. Only in situations where I need to force the Sunday logic would that be flipped to True (and only for the one run-time, not a design-time change).

The logic for our IsSunday gets a touch more complicated as we need to OR || the logic for the parameter name.

DATEPART("weekday", getdate()) == 1 || @[$MyPackage::ForceSunday]

I think I have the parameter name syntax correct but the dollar sign could be elsewhere (this is all from memory)

Next, Next, Next approach

This is a really complex package. Make life easier for everyone - Copy & paste into a package that has Sunday logic and one that has Non-Sunday logic.

Final notes

Regardless of the approach you take, the next problem you will run into is the precedence constraint between both of these containers feeding into the container on the right of the screen. The solid green line is a precedence constraint of Success and Logical AND - so the final box will only ever start if both the Sunday and non-Sunday workflows execute. Logically, that can't happen so you need to double click the line and change it to a Logical OR

2 Data Flow Tasks Linking to one Execute SQL Task

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • I almost didn't post an answer because I'd seen that you'd edited the post, so I sort of guessed an answer was coming. Very thorough, as always. +1. – Eric Brandt Nov 21 '19 at 14:51
  • @EricBrandt but you went with the obvious Execute SQL task with a shoutout to the problem with `DateFirst` so +1 back to ya ;) – billinkc Nov 21 '19 at 14:53
  • OK - that's 2 comments about DATEFIRST that I have no clue what you are talking about! – KeithL Nov 21 '19 at 14:54
  • 1
    :) [DATEFIRST](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-ver15) determines what the system considers the first day of the week, which is reflected in `DATEPART(WEEKDAY, YourDate)`. _Usually_ in the US, `DATEFIRST` is set to 7, so Sunday is considered the first day of the week. _But_ `SET DATEFIRST` can alter that setting, and, as a result, can alter the results of any queries that rely on `DATEPART`, without the code being changed. Like magic, only nightmarish to troubleshoot! – Eric Brandt Nov 21 '19 at 15:08
0

first off all, thank you for your answer.

I might have found an other way which would be a lot easier but I would like to check it with you. In the Server Manager I can create jobs with build in schedule which can be executed on weekdays and weekend days. So I made two jobs, weekend with value false and week with value true. Would it work if I make the following parameters on the sequence containers: In SSDT I have set a parameter for the full load sequence: ForceExecutionValue = false In SSDT I have set a parameter for the incremental load sequence: ForceExecutionValue = true

Will this work or will it still execute both sequences?

Robertjan Rensen
  • 1
  • 2