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
Else
DTSDestination("RetailPrice") = DTSSource("Col010")
End If
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.
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
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
Row.DirectRowToNumeric()
Else
Row.DirectRowToNonNumeric()
End If
End Sub
End Class
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.
-Jamie
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.