SSIS: Implementing IsNumeric( ) Logic Using the Data Flow Script Component

Friday, November 25, 2011
by jsalvo

SSIS does not include an ‘out-of-the-box’ isNumeric( ) function.  Fortunately, this functionality can be implemented using the script component in the data flow.

I am going to make the assumption that readers are familiar with the SSIS script component.  If not, please feel free to read my prior blog post on the subject: SSIS Dataflow Script Component

To implement isNumeric( ) behavior, I used the C# Int32.TryParse method.

TryParse(String, Int32):  Converts the string representation of a number to its 32-bit signed integer equivalent. A return value indicates whether the conversion succeeded.

Here’s a snippet of the code in C#:

public override void Input0_ProcessInputRow(Input0Buffer Row)  
{  
  int BatteryQuantity1Int;

  if (! int.TryParse(Row.BatteryQuantity1Input, out BatteryQuantity1Int))  
    BatteryQuantity1Int = 0;    
  
  //Add an output row and set the column values  
  Output0Buffer.AddRow();  
  Output0Buffer.ModelNumber = Row.ModelNumberInput;  
  Output0Buffer.ModelYear = Row.ModelYearInput;  
  Output0Buffer.ModelName = Row.ModelNameInput;

I also came across a blog post by Dustin Ryan where he implements IsNumeric( ) functionality using the Derived Column Transform.  The post is found here.

Comments

comments powered by Disqus