SSIS - How to Convert Month Name into Month Number in SSIS Package


Sometime we get the Month Name that can be full Month name such as January or we get short Month name Jan in our source file, but in our destination table we want to load the Month Number instead of Month Name.


We can use the Derived Column Transformation and write lengthy expressions to convert  Month Name into Month Number or we can use Script Component to do that. In this blog spot we will use Script Component.

Step 1:

Have your sample data ready, I have created a text file with Full Month Name and short Month Name as shown below.


Step 2: 

Create an SSIS Package, Inside the SSIS Package, Bring Data Flow Task to the Control Flow Pane. Open the Data Flow Task and Bring Flat File Source and create connection to sample file.

Fig 1: Read data from Flat File by using Flat File Source in SSIS Package

Step 3:

Bring the Script Component Transformation and configure as shown below. When we will connect Flat File Source to Script Component it will give us three options Source, Destination, Transformation. In this example click on Transformation.
 Fig 2: Choose the input columns in Script Component

Step 4:

Add the output columns those we are going to create in our Script Component. We will be converting the Month Full Name and Month Short Name into Month Number, As we are creating the Month Number so we will choose the Data type Int.
  Fig 3: Add new columns in Script Component those will be generated in Script Component.

Step 5:

Click on Connection Manager and choose the connection from where the data is coming.
 Fig 4: Choose the connection in Script Component

Step 6:

Click on Script on Left and then Click on Edit Script. You will write the below code. The column names can be changes according to your column names.

Row.MonthNumberFromFullMonthName = DateTime.Parse(Row.MonthFullName + " 01, 1900").Month;
Row.MonthNumberForShotMonthName = DateTime.Parse(Row.MonthShortName + " 01, 1900").Month;

 Fig 5: Write the code in Script Component to convert Month Name to Month Number.

Save the script and then close the Editor window. To see the output I have connected Multicast and then included Data Viewer between Script Component and Multicast. Let's execute the package and see if Month Name is converted to Month Number correctly.
Fig 6: Complete SSIS Package


We can we see that the Month Name is successfully converted to Month Number for Full Month Name as well for Short Month Name.
Fig 7: Output of How to convert Month Name into Month Number in SSIS Package.

1 comment:

  1. A big thanks to you,I was completely unaware of the OLE DB command and its usage with SSIS Upsert because I am a beginner to SQL but this post was like a sea of knowledge for me and helped me to grab everything. Thanks again.

    SSIS Upsert