What logic can I use in my script component to implement Try / Catch invalid dates and replace them with some default value "1/1/1900"

Please include links to code/tutorials or sample code. The sample below does not work for the invalid date "01/01/0200"

Howshould I split the data source to log/redirect to(error input table)these rows with invalid dates? I only want to redirect the rows with invalid dates not the entire batch.

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim FirstDate As Date = New Date(1900, 1, 1)

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

If (Not Row.STARTBILLDATE_IsNull OR Row.STARTBILLDATE < FirstDate)Then FixUpDate(Row.STARTBILLDATE)

'

End Sub

Private Sub FixUpDate(ByRef D As Date)

If D < FirstDate Then

D = FirstDate

End If

End Sub

End Class

Here is a small C# console app that wrote in VS C# 2005. Hope you can use the same code and mould it for script task in SSIS

I found the isdate() T-SQL function invaluable "catching" invalid dates. You would have to stage the data first to use it.

Sample:

Code Snippet

SELECT SalesOrderID, InvalidDate=OrderDate

FROM AdventureWorks.Sales.SalesOrderHeader sod

WHERE ISDATE(sod.OrderDate)=0

GO

Let us know if helpful.

Hi

Here is a small C# console app that wrote in VS C# 2005. Hope you can use the same code and mould it for script task in SSIS