Saturday, 18 December 2010

Do 'something' for all columns in your dataflow

Case
If you have a lot columns that all need to be adjusted with the same function (let's say uppercase). You could use the Derived Column component or the Character Map component to adjust all columns one by one. The Script Component can make your life easier.

Starting point
Col1Col2Col3
testTestTEST
blaBlaBLA
xxxXxxXXX

Desired situation
Col1Col2Col3
TESTTESTTEST
BLABLABLA
XXXXXXXXX

Solution
1) Add a script component to you dataflow.
Script component (transformation)















2) Select all columns (you need) and select ReadWrite.
ReadWrite Input columns



















3) Now the script (I used VB.net in this example). SSIS will generate some default methods. We will only use Input0_ProcessInputRow. You can remove the other methods. The script loops via reflection through all string fields and will uppercase the value. You can adjust the second method to suit your needs.
' This script adjusts the value of all string fields
Imports System
Imports System.Data
Imports System.Math
Imports System.Reflection ' Added
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<microsoft .sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute=".sqlserver.dts.pipeline.ssisscriptcomponententrypointattribute"> _
<clscompliant false="false"> _
Public Class ScriptMain
    Inherits UserComponent

    ' Method that will be started for each record in you dataflow 
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        ' Use Reflection to loop through all the properties of Row:
        ' Example:
        ' Row.Field1            (String)
        ' Row.Field1_IsNull     (Boolean)
        ' Row.Field2            (String)
        ' Row.Field2_IsNull     (Boolean)
        Dim p As PropertyInfo
        For Each p In Row.GetType().GetProperties()
            ' Do something for all string properties: Row.Field1, Row.Field2, etc.
            If p.PropertyType Is GetType(String) Then
                ' Use a method to set the value of each String type property
                ' Make sure the length of the new value doesn't exceed the column size
                p.SetValue(Row, DoSomething(p.GetValue(Row, Nothing).ToString()), Nothing)
            End If
        Next
    End Sub

    ' New function that you can adjust to suit your needs
    Public Function DoSomething(ByVal ValueOfProperty As String) As String
        ' Uppercase the value
        ValueOfProperty = ValueOfProperty.ToUpper()
        Return ValueOfProperty
    End Function

End Class


or in C#
// This script adjusts the value of all string fields
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;            // Added

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent
{
    //  Method that will be started for each record in you dataflow 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // Use Reflection to loop through all the properties of Row: 
        // Example: 
        // Row.Field1 (String) 
        // Row.Field1_IsNull (Boolean) 
        // Row.Field2 (String) 
        // Row.Field2_IsNull (Boolean) 
        foreach (PropertyInfo p in Row.GetType().GetProperties())
        {
            // Do something for all string properties: Row.Field1, Row.Field2, etc. 
            if (object.ReferenceEquals(p.PropertyType, typeof(string)))
            {
                // Use a method to set the value of each String type property 
                // Make sure the length of the new value doesn't exceed the column size 
                p.SetValue(Row, DoSomething(p.GetValue(Row, null).ToString()), null);
            }
        }
    }
 
    // New function that you can adjust to suit your needs 
    public string DoSomething(string ValueOfProperty)
    {
        // Uppercase the value 
        ValueOfProperty = ValueOfProperty.ToUpper();
        return ValueOfProperty;
    }
}

4) Now add a target and run the package to see the result:

Added two data viewers to see the result.

1 comment:

  1. I borrow the c# code for trimming all importing columns. It working well. Thank you!

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...