Stairway to Integration Service: SSIS Expressions

One of the great things about SSIS (SQL Server Integration Services) is the ability to make just about any property dynamic.  By using property expressions can make your SSIS package dynamic at runtime.

In the slide about SSIS expressions that I recently presented, you will learn some important concepts in building SSIS expressions, such as Operator Precedence/Associativity, and Data Mapping between SSIS and SQL Server. You can easily find most-often used string functions, date/time functions, NULL functions, and operators listed for your reference. Some SSIS 2012 new functions for expressions are also listed at the end.

Moreover, as you start using SSIS expression language, you will notice that it is an interesting blend of T-SQL and Visual Basic functions. Sometimes you may be confused about similarity and differences between different languages. Then the notes about syntax difference between SSIS and TSQL will definitely give you a more clear idea.

At last, some tips and tricks and solutions by using SSIS expressions demonstrate not only how to build complicate SSIS expressions step and step but also in a creative way.

As always, let me know what you think. If anything is unclear, I will update to help clarify.

Test Highlighter

<ul class='maketea'>
<li>Tea</li>
<li>Water</li>
<li>Sugar</li>
<li>Milk</li>
</ul>

IF OBJECT_ID('dbo.test') IS NOT NULL
    DROP TABLE dbo.test;

CREATE TABLE dbo.test
    (
      id INT PRIMARY KEY
    , prodName VARCHAR(20)
    );

Dim fileName As String = "C:\mydir\test.txt"
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

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;
        }
   }