How can I use Contains() to evaluate InputBuffer rows when using SSIS Script Transform?

Question

I am learning to use an SSIS 2012 Script Component (C#) to transform values based on a list of possible conditions.

I have successfully used the == operator to compare the input row to exact string values in a switch block, but when I tried the .Contains() method in an if-else block, all rows were returned as case else.

I show both code attempts below. I am using the .Contains() method and double-checked with MSDN. It looks to me as if I am using the method correctly.

Is there a reason why I can do an exact == operator evaluation but not a .Contains() evaluation?

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    // This block does **not** succeed in evaluating the Row.MealCode value 
    // with the .Contains("string") method. All rows evaluate as case else.

    if (Row.MealCode.Contains("FREE"))
    {
        Row.tMealCode = "Free";
    }
    else
    {
        Row.tMealCode = "Else";
    }


    // This code successfully evaluates the value of MealCode 
    // in each row and correctly outputs it as a tMealCode value.

    switch (Row.MealCode)
    {   
        case "Free lunch":                
        case "FREE":
            Row.tMealCode = "Free";
            break;
        case "Reduced Lunch":
        case "RED":
            Row.tMealCode = "Reduced";
            break;
        case "REG":
            Row.tMealCode = "Regular";
            break;
        default:
            Row.tMealCode = "Else";
            break;
    }
}

To provide more context, this screenshot shows where I have placed the Script Component immediately before the OLE DB Destination task.

The script component placed between the Data Conversion task and the OLE DB Destination task

score 1 · Accepted Answer · edited May 23 '17 at 10:29

From the referenced MSDN

Returns a value indicating whether a specified substring occurs within this string.

Assume your source data contains the following values

FREE
free
Free
FRee
FREe

.Contains("FREE") is only going to match the first value because it is going to do a case sensitive comparison.

You've done much the same in your switch statement - you've coded for the various casings you've encountered.

You need to either do a Case Insensitive comparison How can I do a case insensitive string comparison?

or if you're set on using the Contains method, ensure both arguments are the cased appropriately.

if (Row.MealCode.ToUpper().Contains("FREE".ToUpper()))

enter image description here

Biml

Biml, the Business Intelligence Markup Language, is the platform for business intelligence. Here, we're going to use it to describe the ETL. BIDS Helper, is a free add on for Visual Studio/BIDS/SSDT that improves the development experience. Specifically, we're going to use the ability to transform a Biml file describing ETL into an SSIS package. This has the added benefit of providing you a mechanism for being able to generate exactly the solution I'm describing versus clicking through many tedious dialogue boxes.

After installing it, add a new Biml file to your SSIS project and edit line 5 to point to a valid SQL Server instance. Right click on the biml file and select Generate SSIS Package.

Here's an example of the Biml required to generate an SSIS package with your intended logic using Contains

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <Connection
        Name="tempdb"
        ConnectionString="Data Source=.\dev2008;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
            />
  </Connections>
  <Packages>
    <Package Name="so_31330881">
      <Tasks>
        <Dataflow Name="DFT Sample">
          <Transformations>
            <OleDbSource ConnectionName="tempdb" Name="OLESRC dbo_Source">
              <DirectInput>SELECT D.MealCode FROM (VALUES ('FREE'), ('free'), ('Free')) AS D(MealCode);</DirectInput>
            </OleDbSource>
            <ScriptComponentTransformation ProjectCoreName="SC_31330881" Name="SCR Transform values">
              <ScriptComponentProjectReference ScriptComponentProjectName="SC_31330881" />
            </ScriptComponentTransformation>
            <DerivedColumns Name="DER Placeholder" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>

    <ScriptProjects>
        <ScriptComponentProject ProjectCoreName="SC_31330881" Name="SC_31330881">
    <Files>
        <File Path="main.cs">
using System;
using System.Data;
using System.Web.Services;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
      // if (Row.MealCode.Contains("FREE"))
      if (Row.MealCode.ToUpper().Contains("FREE".ToUpper()))
      {
          Row.tMealCode = "Free";
      }
      else
      {
          Row.tMealCode = "Else";
      }      
    }
}
</File>
      <File Path="Properties\AssemblyInfo.cs">
        using System.Reflection;
        using System.Runtime.CompilerServices;

        //
        // General Information about an assembly is controlled through the following
        // set of attributes. Change these attribute values to modify the information
        // associated with an assembly.
        //
        [assembly: AssemblyTitle("SC_31330881")]
        [assembly: AssemblyDescription("")]
        [assembly: AssemblyConfiguration("")]
        [assembly: AssemblyCompany("")]
        [assembly: AssemblyProduct("SC_31330881")]
        [assembly: AssemblyCopyright("Copyright @  2014")]
        [assembly: AssemblyTrademark("")]
        [assembly: AssemblyCulture("")]
        //
        // Version information for an assembly consists of the following four values:
        //
        //      Major Version
        //      Minor Version
        //      Build Number
        //      Revision
        //
        // You can specify all the values or you can default the Revision and Build Numbers
        // by using the '*' as shown below:

        [assembly: AssemblyVersion("1.0.*")]
      </File>
    </Files>
    <AssemblyReferences>
      <AssemblyReference AssemblyPath="System" />
      <AssemblyReference AssemblyPath="System.Data" />
      <AssemblyReference AssemblyPath="System.Web.Services" />
      <AssemblyReference AssemblyPath="System.Windows.Forms" />
      <AssemblyReference AssemblyPath="System.Xml" />
      <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript.dll" />
      <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap.dll" />
      <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap.dll" />
      <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost.dll" />
    </AssemblyReferences>
          <InputBuffer Name="Input 0">
            <Columns>
              <Column CodePage="1252" DataType="AnsiString" Length="10" Name="MealCode" UsageType="ReadOnly" />
            </Columns>
          </InputBuffer>
    <OutputBuffers>
      <OutputBuffer IsSynchronous="true" Name="Output 0">
        <Columns>
          <Column CodePage="1252" DataType="AnsiString" Length="10" Name="tMealCode" />
        </Columns>
      </OutputBuffer>
    </OutputBuffers>
  </ScriptComponentProject>
</ScriptProjects>
</Biml>

This is what solved my problem. `if (Row.MealCode.ToUpper().Contains("FREE".ToUpper()))`. I was able to use this approach to set up the other `else if` statements. I did not consider that `Contains()` is case sensitive. — , Jul 10 '15 at 02:42

How can I use Contains() to evaluate InputBuffer rows when using SSIS Script Transform?

1 Answers1

Biml