SSIS - Filter rows *and* add columns in script component

• Pete Bishop

  Default port

  Points: 1478

  More actions

  March 31, 2021 at 8:42 am

  #3863904

  Is it possible to have a transformation script component which filters rows as well as adding new output columns?

  I have a script component with "Output 0":

  • Exclusion Group = 1
  • SynchronousInputID = Script Component.Inputs[Input 0]
  • 2 x additional columns

  In my code I have:

  Row.newcol1 = "Value 1";
  Row.newcol2 = "Value 2";
  
  if (Row.oldcol1.Length <= 20)
  {
      Row.DirectRowToOutput0();
  }
  

  But I'm getting the errors:

  CS0229Ambiguity between 'Input0Buffer.newcol1' and 'Input0Buffer.newcol1'
  CS0229Ambiguity between 'Input0Buffer.newcol2' and 'Input0Buffer.newcol2'

  So I can filter rows *without* adding new columns and I can add new columns *without* filtering data.

  Is there a way to add columns *and* filter data without having to create a new set of output columns and copying over the inputs to the outputs?

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  March 31, 2021 at 9:37 am

  #3863921

  I don't know the answer, but may have an alternative solution.

  Use a Conditional Split to redirect rows which you do not want, by sending them to an unused output.

  Your Script Component can be simplified once this is in place.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin