Monday, February 15, 2010

Explicit Data Conversion in SSIS Using the Script Component

There are several ways to convert data in an data flow in SSIS. Here is an example of Explicit Data Conversion in SSIS Using the Script Component. This piece of visual basic code converts a string in to a datetime or SSIS (DT_DBTIMESTAMP)

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Stage1Buffer.AddRow()
If Not (String.IsNullOrEmpty(Row.Stage1Date)) Then
Stage1Buffer.Stage1Date = Convert.ToDateTime(Row.Stage1Date)
End If

Stage1Buffer.EndOfRowset()
End Sub

The if condition prevents the task from failing if an empty string is encountered at Convert.ToDateTime() call. So make sure to include it.


Another way to cast DT_STR to a DBTIMESTAMP is by creating a derived column component and adding a new column with the following expression:
CaseAssignedDate == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)CaseAssignedDate

1 comment: