SSIS - How to Convert Month Number into Month Name in SSIS Package By Using Script Component

Scenario:

We have data in flat file that has Month Number column. We need to process this data and finally load to an Excel Destination. Instead of Month Number we want to load Month Name in Excel Destination. 

Solution:

There are multiple ways to achieve this. In this example we will be using Script Component to convert the Month Number into Month Full Name and Month Short Name. In my previous post I have used the Script Component to convert Month Full Name and Month Short Name into Month Number if you like to see. Click Here.

Step 1:

Prepare the sample data if you don't have real time data to work on. Here is sample data that I will be using for sample SSIS Package.

MonthNumber,DataColumn
1,SampleData
2,SampleData
3,SampleData
4,SampleData
5,SampleData
6,SampleData
10,SampleData
11,SampleData
12,SampleData
7,SampleData
8,SampleData
9,SampleData

Step 2:

Create new SSIS Package in your SSIS Project. Bring the Data Flow Task to the Control Flow Pane and then drag Flat File Source on Data Flow Pane and create connection to the sample data file. Make sure you change the column Data type for MonthNumber to DT_I4 as by default it will be DT_STR.
Fig 1: Create Flat File Connection Manager

Fig 2: Choose Data Type for MonthNumber as DT_I4

Step 3:

Bring the Script Component to Data Flow Pane and then connect Flat File Source to it. There are three modes (Source, Destination and Transformation) in which we can use Script Component. For this SSIS Package we will be using Script Component as Transformation.

Configure the Script Component as shown below.

 Fig 3: Choose Input columns for Script Component

 
 Fig 4: Add Output Columns in Script Component



Fig 5: Choose the Connection Manager that Script Component will use.


Fig 6: Choose the Script Language in Script Component and Edit Script.



In Editor, Write the code as shown below and then hit save and close the Editor.
Fig 7: Write Script in Script Component to Get Month Name from Month Number in SSIS Package

Output:

Now you can write the output to your required destination. For for test purpose, I am connecting the output to Multicast and added Data Viewer to show to results.

Fig 8: Complete SSIS Package to convert Month Number into Month Short Name and Full Name

Execute the SSIS Package and see the output.


 Fig 9: The Month Number conversion to Month Name in SSIS Package output

2 comments:

  1. Thank you so much for exploring the best and right information.

    SSIS Upsert

    ReplyDelete
  2. In this article, we are going to help you opt for the right one. video clips to audio

    ReplyDelete