TechBrothersIT is the blog spot and a video (Youtube) Channel to learn and share Information, scenarios, real time examples about SQL Server, Transact-SQL (TSQL), SQL Server Database Administration (SQL DBA), Business Intelligence (BI), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), Data Warehouse (DWH) Concepts, Microsoft Dynamics AX, Microsoft Dynamics Lifecycle Services and all other different Microsoft Technologies.
SSIS - How to Convert Month Number into Month Name in SSIS Package By Using Script Component
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.
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.
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.
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
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
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