Many ex-DTS users are miffed that the SSIS expression language does not have an equivalent of the VB Script IsNumeric() function that is used so extensively in DTS and to be honest, its a fair shout.
The following code is very typical in DTS packages:
If IsNumeric(RetailPrice) = False Then
DTSDestination("RetailPrice") = Empty
DTSDestination("RetailPrice") = DTSSource("Col010")
As I said above, the SSIS expression language does not have an IsNumeric() function so the Derived Column component cannot be used to do the same. Script Component to the rescue!!! The following code inside a script component will achieve the same.
Public Class ScriptMain
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim retNum As Double
'use TryParse to test if the value is numeric or not
If System.Double.TryParse(Row.Column, retNum) Then
In this particular code snippet the row is sent to one of two outputs depending on the result of the TryParse() method, but is up to you what you do with the row.
If you want to know more about multiple outputs from a script component then go here:
SSIS Nugget: Multiple outputs from a synchronous script transform
I have attached the package that I built containing this code so you can see exactly how it works.
UPDATE, 13th March 2008: Andy Mitchell has devised a way of doing this in the Derived Column component and has posted his little trick below in the comments section. Does his technique work for you? If so, leave a comment and let others know.