July 27, 2010 at 7:26 am
Hi , i am using script task to convert from string to date datatype.source is flat file and destination is sql server 2005 database.
this is the code i am using :
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Trim(Row.REQUESTDATE) = "" Then
Row.requestdateFIX = Nothing
Else
Row.requestdateFIX = FixDate(Row.REQUESTDATE)
End If
If Trim(Row.SCHEDULESHIPDATE) = "" Then
Row.scheduleshipdateFIX = Nothing
Else
Row.scheduleshipdateFIX = FixDate(Row.SCHEDULESHIPDATE)
End If
If Trim(Row.PROMISEDATE) = "" Then
Row.promisedateFIX = Nothing
Else
Row.promisedateFIX = FixDate(Row.PROMISEDATE)
End If
If Trim(Row.LINEENTERDATE) = "" Then
Row.lineenterdateFIX = Nothing
Else
Row.lineenterdateFIX = FixDate(Row.LINEENTERDATE)
End If
If Trim(Row.QTYUPDATEDATE) = "" Then
Row.qtyupdatedateFIX = Nothing
Else
Row.qtyupdatedateFIX = FixDate(Row.QTYUPDATEDATE)
End If
If Trim(Row.EXTRACTEXECDATE) = "" Then
Row.extractexecdateFIX = Nothing
Else
Row.extractexecdateFIX = FixDate(Row.EXTRACTEXECDATE)
End If
End Sub
Function FixDate(ByVal sBadDate As String) As Date
Dim sYear, sMonth, sDay As String
sYear = Right(sBadDate, 2)
sMonth = Mid(sBadDate, 4, 3)
sDay = Left(sBadDate, 2)
If sYear > "80" Then
sYear = "19" & sYear
Else
sYear = "20" & sYear
End If
Select Case sMonth
Case "JAN"
sMonth = "01"
Case "FEB"
sMonth = "02"
Case "MAR"
sMonth = "03"
Case "APR"
sMonth = "04"
Case "MAY"
sMonth = "05"
Case "JUN"
sMonth = "06"
Case "JUL"
sMonth = "07"
Case "AUG"
sMonth = "08"
Case "SEP"
sMonth = "09"
Case "OCT"
sMonth = "10"
Case "NOV"
sMonth = "11"
Case "DEC"
sMonth = "12"
End Select
Return CDate(sYear & "-" & sMonth & "-" & sDay)
End Function
End Class
but i am getting the below runtime error:
[Script Component [2329]] Error: System.InvalidCastException: Conversion from string "19AM-9/2-6/" to type 'Date' is not valid. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
can anyone please help me?i am new for ssis ....
July 27, 2010 at 8:03 am
I found this to convert string to a date datatype while looking for answer to above error:
Dts.Variables("someDateTimeVariable").Value = GetDateFromString(Dts.Variables("someStringVariable").Value.ToString)
but i don't know how to use it or declare variables for it?can someone guide me...
July 27, 2010 at 8:09 am
Can you show us a small subset of the data you are importing? If the formatting of the date fields is consistant, it may be easier to import all the character data directly into a staging table and then use SQL to move the data to the final destination and perform the necessary data conversions.
July 27, 2010 at 8:56 am
i have attached the source file :
July 27, 2010 at 10:07 am
I have created a staging table in the same database where i have the destination table too,and loaded all the rows into the staging table successfully,now i am going to convert string datatype to date and load into destination table.i believe this is what you told to do.
But still i want to know how to avoid the error i got in script task.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply