I am trying to automate an SSIS package as in the current format it is using the data from tables from the past three years.

The layout of the table names is TableName_YYYYMM for 2 of them and TableName_YYYYjan or feb etc for the other.

How would I set out the for loop with a date variable so that the package is getting the data from the tables from 3 years ago for each month until the current month?

I am fairly new at SSIS so I have been unable to find any solution yet as I am not 100% sure on how I would go about implementing it.

CodePudding user response：

It's hard to say, for me at least, without seeing the details of what you're doing. While you're building out dates, you're also not as you're only working with year and month so using a date data type probably isn't going to help.

Create 5 SSIS variables

Year - Int32 - 0
Month - Int32 - 0
YYYYMM - String - Evaluate as Expression (DT_WSTR, 4) @[User::Year] RIGHT("0" (DT_WSTR, 2) @[User::Month], 2)
YYYYMonth - String - Evaluate as Expression (DT_WSTR, 4) @[User::Year] @[User::MonthAbr]
MonthAbr - String - Evaluate as Expression

Expression

(@[User::Month] == 1) ? "Jan" : 
(@[User::Month] == 2) ? "Feb" : 
(@[User::Month] == 3) ? "Mar" : 
(@[User::Month] == 4) ? "Apr" : 
(@[User::Month] == 5) ? "May" : 
(@[User::Month] == 6) ? "Jun" : 
(@[User::Month] == 7) ? "Jul" : 
(@[User::Month] == 8) ? "Aug" : 
(@[User::Month] == 9) ? "Sep" : 
(@[User::Month] == 10) ? "Oct" : 
(@[User::Month] == 11) ? "Nov" : 
(@[User::Month] == 12) ? "Dec" : "UNK"

At this point, if I change the value of Year or Month, those three variable change so that simplifies our problem to "how do we change those two?"

If I have whole year boundaries to work with i.e. 2000 to 2021 Jan to Dec every year, I'd use something like this package. A nested set of For Loop Containers. Outer enumerates years from 2000 to 2021. The inner enumerates 1 to 12. The Sequence Container inside is where I do the actual work. In this case, I simply print the value of my user variables to show it works.

For Loop - Year

Configuring the variable @[User::Year]

For Loop - Month

Configuring the variable @[User::Month]

My Script Task is my much beloved (by me at least)

Inside the Sequence Container between the first task (an empty Sequence Container is an excellent anchor as it is a logical construction and requires no configuration) and the second task and set the Evaluation operation as Expression and Constraint. Value is Success. Expression is @[User::IsValid]

Disable approach

The thought here is that we'll set the Disable property of SEQC Do Work via Expression. I find this a little confusing from a maintenance perspective as we need to work with the inverse of IsValid because we only want to Disable when it's not valid.

Right click on SEQC Do Work
Properties
Expressions
Select Disable -> !@[User::IsValid]
Ok
Ok

Do I need to create all these variables

Yes. You might even want some more. Anything that has an expression on it cannot be debugged except through observation of the computed result. I can't put a break point between (DT_WSTR, 4) @[User::Year] and RIGHT("0" (DT_WSTR, 2) @[User::Month], 2) to see whether I'm building out a 2 character month "number." The only thing I can do is look at the value of a variable. Which means, do not use anything in a Task or Containers expression that is not a single Variable. I do violate it above with Disable and that's why I also note it as a maintenance overhead/technical debt.

If I get any weird behaviour while I'm testing this, I can set a breakpoint immediately before the weird task/container and inspect my variables and trace back to why it's weird. If I had the same expression from YYYYMonth but all in a single go attached to an OLE DB Source, good luck trying to figure out why it's pulling incorrect data.

How can I make the boundaries dynamic?

Add a pair of Variables

YearInitial - Int32 - 2000
YearTerminal - Int32 - Evaluate as expression YEAR(@[System::StartTime])