Is there a way to stop a conditional split after a certain date?

Question

I have an SSIS package with a conditional split that essentially checks if one number is bigger than another. The number is actually a financial period but this is stored as an int in a "yyyymm" format e.g. "201911"

The conditional split sends anything less than or equal to the current financial period to be loaded into our reconciliation software and anything greater than to a holding table.

After 3rd December we will no longer need to check this as all data will be sent to the same place.

I am trying to avoid the need to update the package on the day and I would like to build in some sort of date check to change how the conditional split works

I've considered checking if the period number is greater than or equal to "201912" in conjunction with our current condition but, we regularly receive transactions that would fall into future or past periods and would then get split incorrectly.

Our current conditions are;

intPeriodNo <= @[User_var::intBankPeriodNo] This will be loaded into the rec software intPeriodNo > @[User_var::intBankPeriodNo] This will be loaded into the holding table

intPeriodNo is derived from the source data and intBankPeriodNo comes from our period control table.

I want to be able to stop the conditional split from splitting the data on 3rd December regardless of the period number and force all data to be loaded into the rec software.

I have some experience with SSIS but not a huge amount so any help or ideas are greatly appreciated.

Answer 1

You could edit both of your current conditionals to check today's date as part of the condition.

(GETDATE()>(DT_DBDATE)("2019-12-03")) || (intPeriodNo <= @[User_var::intBankPeriodNo])

Current date greater than 2019-12-03 OR current condition -> Choose this path.

And then,

(GETDATE()<=(DT_DBDATE)("2019-12-03")) && intPeriodNo > @[User_var::intBankPeriodNo]

Current date less than or equal to 2019-12-03 AND current condition -> Choose this path.

After 2019-12-03, all rows will go down path one, and you can go back and edit the package at your leisure whenever you get around to it.

Answer 2

in a sql task save something like this in a variable

SELECT CONVERT(datetime,SUBSTRING('201911',1,4)+'-'+SUBSTRING('201911',5,2)+'-01',121)

in a condition split you could use this to manage your flow

getdate() < @[User_var::variable]