Script Task: Find the Day of Week in SSIS

Problem

How to find out the day of week in SSIS to achieve the same result as DATENAME( dw, datecolumn ) as in TSQL? For example, we need to find out “2013-11-24” is Sunday or Saturday.

Solution

Suppose FROM_SERVICE_DATE is the date column to derive the info about day of week. Below are two options. Using “Script Task” seems more complicate than directly using “Derived Column”. But it is more readable, isn’t it? Moreover, from this example, you can learn to use “Script Task” to handle multiple CASE situations.

Using “Derived Column”

Below is the expression to input in “Derived Column” task by using nested Conditional Operator in SSIS.

DATEPART("dw",FROM_SERVICE_DATE)==1?"Sunday":DATEPART("dw",FROM_SERVICE_DATE)==2?"Monday":DATEPART("dw",FROM_SERVICE_DATE)==3?"Tuesday":DATEPART("dw",FROM_SERVICE_DATE)==4?"Wednesday":DATEPART("dw",FROM_SERVICE_DATE)==5?"Thursday":DATEPART("dw",FROM_SERVICE_DATE)==6?"Friday":DATEPART("dw",FROM_SERVICE_DATE)==7?"Saturday":""
 
Using “Script Task”

Step 1: Derive a new column with "dayNoOfWeek” as Name for the numeric number of day of week, such as 1st or 6th of the week.

Step 2: Drag and drop Script Component task to the Data Flow Task (DFT) pane, you will be prompted to select script component type as shown below. Check “Transformation”. Then connect it with the above Derived Column Task.

Step 3: Under “Input Column” page, check “dayNoOfWeek” as shown below.

Step 4:
Under “Inputs and Outputs” page, expand “Output0” and select “Output Columns”. Click “Add Column” button. A new column will appear. You can rename it to “NameOfWeekDay”.
Next, modify the column properties on the right. Set the new column to be a string with length of 10.

Now go back to “Script” page on the top, select the “ScriptLanguage” to be C# related. Then click “Edit Script” button at the low right corner.

Enter the following codes in the Sub function Input0_ProcessInputRow.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        switch (Row.dayNoOfWeek) {             
            case 1: Row.NameOfWeekDay = "Sunday";       break;
            case 2: Row.NameOfWeekDay = "Monday";       break;
            case 3: Row.NameOfWeekDay = "Tuesday";      break;
            case 4: Row.NameOfWeekDay = "Wednesday";    break;
            case 5: Row.NameOfWeekDay = "Thursday";     break;
            case 6: Row.NameOfWeekDay = "Friday";       break;
            case 7: Row.NameOfWeekDay = "Saturday";     break;
        }
   }

If you prefer to Visual Basic as your script language, add the following code in the sub function of Input0_ProcessInputRow.       

SELECT CASE Row.dayNoOfWeek
            Case 1
                Row.NameOfWeekDay = "Sunday"
            Case 2
                Row.NameOfWeekDay = "Monday"
            Case 3
                Row.NameOfWeekDay = "Tuesday"
            Case 4
                Row.NameOfWeekDay = "Wednesday"
            Case 5
                Row.NameOfWeekDay = "Thursday"
            Case 6
                Row.NameOfWeekDay = "Friday"
            Case 7
                Row.NameOfWeekDay = "Saturday"
END SELECT
Next, build and close the Script Edit window.