SSIS – Getting Partition Names for the past 12 months

I had a situation where I was using a conditional split, so that when data was coming in from my source tables it would dynamically go into the correct partitioned table.

 

Below is the script that I used, as well as for reference the conditional split

 

— ==================================================================================================================

— What we are doing here is to create a Proc so that we can get the PartitionNames going back 12 months.

— While for the first time this is a manual process this can be used for other Parition Schemes going foward.

— The only thing that will need to change if the Partition Naming convention is the same is the Partition name

— ==================================================================================================================

 

— ==================================================================================================================

— 1. Here we are getting our current partition naming scheme into a variable.

— Example of partition scheme: Mart_TF_InternetSales_2014_02

— ==================================================================================================================

Declare@PartitionNameasvarchar(30)

Set@PartitionName=

       (

              Select‘Mart_TF_InternetSales_’

       )

 

— ==================================================================================================================

— 2. Here we are getting our Current Month Details.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@CurrentMonthNameasvarchar(8)

Set@CurrentMonthName=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-0, 0)))

       )

 

— ==================================================================================================================

— 3. Here we are getting our 1 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@OneMonthBackasvarchar(8)

Set@OneMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-1, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 2 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@TwoMonthBackasvarchar(8)

Set@TwoMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-2, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 3 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@ThreeMonthBackasvarchar(8)

Set@ThreeMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-3, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 4 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@FourMonthBackasvarchar(8)

Set@FourMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-4, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 5 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@FiveMonthBackasvarchar(8)

Set@FiveMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-5, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 6 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@SixMonthBackasvarchar(8)

Set@SixMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-6, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 7 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@SevenMonthBackasvarchar(8)

Set@SevenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-7, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 8 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@EightMonthBackasvarchar(8)

Set@EightMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-8, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 9 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@NineMonthBackasvarchar(8)

Set@NineMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-9, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 10 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@TenMonthBackasvarchar(8)

Set@TenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-10, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 11 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@ElevenMonthBackasvarchar(8)

Set@ElevenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-11, 0)))

       )

 

— ==================================================================================================================

— 4. Here we are getting our 12 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

— ==================================================================================================================

Declare@TwelveMonthBackasvarchar(8)

Set@TwelveMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-12, 0)))

       )

Select

               @PartitionName+@CurrentMonthNameasCurrentMonthParititionName

              ,@PartitionName+@OneMonthBackasOneMonthBackPartitionName

              ,@PartitionName+@TwoMonthBackasTwoMonthBackPartitionName

              ,@PartitionName+@ThreeMonthBackasThreeMonthBackPartitionName

              ,@PartitionName+@FourMonthBackasFourMonthBackPartitionName

              ,@PartitionName+@FiveMonthBackasFiveMonthBackPartitionName

              ,@PartitionName+@SixMonthBackasSixMonthBackPartitionName

              ,@PartitionName+@SevenMonthBackasSevenMonthBackPartitionName

              ,@PartitionName+@EightMonthBackasEightMonthBackPartitionName

              ,@PartitionName+@NineMonthBackasNineMonthBackPartitionName

              ,@PartitionName+@TenMonthBackasTenMonthBackPartitionName

              ,@PartitionName+@ElevenMonthBackasElevenMonthBackPartitionName

              ,@PartitionName+@TwelveMonthBackasTwelveMonthBackPartitionName

 

And here is the conditional split

NOTE: The reason that it is in RED is because I changed the actual datetime column that I have in my dataset.

NOTE 2: Normally this would be black to indicate that there are no conditional errors