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

• Pete Bishop

  Default port

  Points: 1485

  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: 247158

  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.

  ---