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

Scenario:

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.

Solution:

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.


MonthFullName,MonthShortName,TestData
January,Jan,Data
February,Feb,Data
March,Mar,Data
April,Apr,Data
May,May,Data
June,Jun,Data
July,Jul,Data
August,Aug,Data
September,Sep,Data
October,Oct,Data
November,Nov,Data
December,Dec,Data

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

Output:

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.




No comments:

Post a Comment