SSIS - How To Get Day Name from Week Day Number By Using Script Component In SSIS

Scenario:

In this post we will learn how to convert Week Day Number (1,2,3,4,5,6,7) to Day name ( Monday,Tuesday, Wednesday,Thursday,Friday,Saturday and Sunday) by using Script Component.

Solution:

Let's say we are getting data in flat file that has week Day numbers and we need to convert o Day Name before we insert into our destination. We can use expressions in Derived Column Transformation or we can use Script Component to do that. In this example we are going to use Script Component.


 WeekDayNumber,DataColumn
1,SampleData
2,SampleData
3,SampleData
4,SampleData
5,SampleData
6,SampleData
7,SampleData
6,SampleData
7,SampleData
3,SampleData
1,SampleData

Step 1:

Create an SSIS Package. Bring the Data Flow Task to Control Flow Pane and then bring Flat File Source and create connection to Flat File Source. If you are extracting data from Database you don't need to use Script Component. You can always use SQL Query to convert it to required format and then process it and load to destination. As we are reading the data from Flat File, we can not write SQL query so we have to use Derived Column Transformation or Script Component.

 

Fig 1: Create Connection to Source Flat File

As we know that the WeekDayNumber is going to be integer value. Change the data type in Flat File Connection Manager from DT_STR to DT_I4 that is integer.

Fig 2: Change data type of WeekDayNumber Column

Step 2:

Bring Script Component and connect to Flat File Source. We are using Script Component as Transformation in this example. Add a new new column DayName of Data Type DT_STR ( String).

Fig 3: Choose Input Columns in Script Component

Add a new output column with DayName of data type string (DT_STR).

Fig 4: Add new column DayName of data type string in Script Component

Add connection manager in Script Component as shown below.

Fig 5: Add connection to Script Component

Click on Script on left then choose the script language you like to script. For this example, I am using C#. Click on Edit Script and then write the script as shown below.

Fig 6: Add script to Script Component to get Day Name from Day Number

Outupt:

For test purpose I have added the Multicast Transformation and then added Data Viewer to display data. Let's run the below SSIS Package and see the output.

Fig 7: SSIS Package to Convert week day number to week day name

Fig 8: Output of converting day number to Day name in SSIS Package