SQL Server BIDS data manipulation

Question

I have an Excel Source and an OLE database destination. For the column mappings I need to first perform some data manipulations on the columns.

How do I get a substring from one of the source columns to be mapped to one of the destination columns?

How can I use a lookup table on one of my columns to get the value associated with the lookup table and then map that value to one of my destination columns?

I would like to use C# scripts as much as needed.

Thanks!!

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

You can use Derived column transformation to get the substring of a source column .

Expression

 SUBSTRING(COLUMN_NAME,START,END)

In the derived column tab select add as a new Row .Then map this new column in the destination .For the lookup transformation please see my reply to one of the SO questions Lookup Transformation

In order to do this in C# drag a script component onto the designer.Select the input and outputs and then add a new column ( Example NewColumn as the name)

   public class ScriptMain:UserComponent
  {


  public override CreateNewOutputRows()
  {
    int space = Row.SourceColumnName.IndexOf(' ');
  Row.NewColumn= Row.SourceColumnName.Substring(0,space)

  //If you need to retrieve the characters after second dot then
  //int firstDot=Row.Column.IndexOf('.')
  //int secondDot=Row.Column.IndexOf('.',firstDot+1)
 // Row.Newcolumn=Row.SourceColumnName.Substring(secondDot,Row.SorceColumnName.Length)
  }
  }

I need more generality for START and END. Such as the first whitespace character position, or the second dot position. I know this is easily acheivable in a programming language. So how do I do this in a C# script? — CodeKingPlusPlus, Jun 08 '12 at 15:19

SQL Server BIDS data manipulation

1 Answers1