Loading the DTS Package

[adimulam]

Hi,
I am loading a flat file into SQL table through DTS package. The data looks like as follows..

123 1/1/05
123 1/2/05
234 1/3/05
234 0/2/05
234 1/6/05

It is two field data. The second field is defined as Date data type. If there is a date value coming as 0/2/05 in the data file, file stops loading. Is there any way I can make DTS continue loading the rest of the data (Even there is a type mismatch for one record) and report the details of the bad statement in an exception file?.

In the above case first 3 records and 5th record should be loaded. The details of 4th record should be reported in the exception file.

Any help is appreciated.

 

[RAS56]

Have you thought about loading the data as it is into a staging table and then processing the data to only import valid date values into your final destination. That should allow you to report on bad values.

 

[FlashMerlot]

Faced a similar problem ... what we did was write a transformation script to convert "bad-dates" into nulls. Here's that script. Hope it helps.

Function Main()
D_Date = DTSSource("Col009")
If Trim(D_Date) <> "" And Trim(Left(D_Date, 10)) <> "" And Not Isnull(D_Date) Then
mm = Mid(D_Date, 1, 2)
dd = Mid(D_Date, 4, 2)
ccyy = Mid(D_Date, 7, 4)
hr = 0
mn = 0
sec = 0
If len(D_Date) >= 14 Then
hr = Mid(D_Date, 11, 2)
mn = Mid(D_Date, 13, 2)
If hr = 24 And mn = 0 Then
hr = 23
mn = 59
sec = 59
End If
End If
If ccyy = 1001 Then
D_Date = Left(D_Date, 6) & 2001
End If
If ccyy = 1002 Then
D_Date = Left(D_Date, 6) & "2002"
End If
ccyy = Trim(Mid(D_Date, 7, 4))
If ccyy < 4 Then ccyy = ccyy + 2000
If ccyy > 3 And ccyy < 100 Then ccyy = ccyy + 1900
If ccyy < 1900 Then
D_Date = Null
Else
D_Date = Left(D_Date, 6) & ccyy
If Not IsDate(D_Date) Then
If mm = 2 And dd = 29 Then
D_Date = "03/01/" & Right(D_Date, 4)
End If
End If
If IsDate(D_Date) Then
If Not Isnull(D_Date) Then D_Date = D_Date & " " & hr & ":" & mn & ":" & sec
Else
D_Date = Null
End If
End If
End If
If Not IsDate(D_Date) Then
D_Date = Null
End If
DTSDestination("SP_IN_DATETIME") = D_Date
Main = DTSTransformStat_OK
End Function