SSIS–Conditional Split based on Date

Below is a quick blog where I am using a conditional split based on your Date Column, so that you can then output your data to different tables or destination data flow tasks.

I also found that there are bits and pieces of information as to how to use the Expressions within the Conditional Split. So this might help someone looking on how to achieve this.

Example Scenario

·         In my example I am going to conditionally split the data by Month, so that when the conditional split has completed it will then direct the output to either the current month or the previous month.

·         The column which contains the DateTime data type is called:

o    DateTimeCreated

1.       Go into your Data Flow Task and either create your query or stored Proc for your Data Flow Source

2.       Then from your SSIS Toolbox drag in the Conditional Split

a.       

3.       Now double click on the Conditional Split and you will need to configure it with the following for the Current Month

a.        Where it says Output Name, put in CurrentMonth

b.       Then where it says Condition put in the following below, which will be explained afterwards how this works:

DATEPART(“MONTH”,ImportDateTime)==DATEPART(“MONTH”,GETDATE())

                                                               i.      What we are doing above is using the DatePart and getting the Month For our DateTime column which is called DateTimeCreated, highlighted in RED

                                                              ii.      Next we are using the compare or equal to for our SSIS Condition, highlighted in PURPLE

                                                            iii.      The final part is what you are comparing it to, so here we are comparing it to the current date, highlighted in ORANGE

c.        Now to do this for the Previous Month there is only one addition to your Condition, which is shown below

                                                               i.      Put in the Output Name of PreviousMonth

                                                              ii.      DATEPART(“MONTH”,DateTimeCreated) == DATEPART(“MONTH”,GETDATE())– 1

                                                            iii.      Here we have put a -1 at the end of our comparison, because this is outside of the DatePart function which is for our Month it is subtracting one month from our Current Date, highlighted in GREEN

d.       This is what it will look like once completed:

e.      

4.       Then the final step is to then drag your Data Flow Destination flow item to your Data Flow Destination

a.        Once dropped onto your Data flow Destination you will then get the Input Output Selection Window.

b.       Click on the Drop down and depending on your Data Flow Destination select your Output

                                                               i.      In the screenshot below it was the CurrentMonth

                                                              ii.     

5.       So once completed it will look like the following:

6.