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
Col1 | Col2 | Col3 |
test | Test | TEST |
bla | Bla | BLA |
xxx | Xxx | XXX |
Desired situation
Col1 | Col2 | Col3 |
TEST | TEST | TEST |
BLA | BLA | BLA |
XXX | XXX | XXX |
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. |
I borrow the c# code for trimming all importing columns. It working well. Thank you!
ReplyDelete