February 20, 2005 at 7:21 am
Hi,
I'm new to SSIS and I have a simple question (I think).
I have a flat file containing serveral columns and two of
them contains date values. The format of the date for one
column is 20040429 and for the other is 29-04-2004. The
datatype of the first date column in the flatfile manager
is DT_I4 and for the second DT_STR.
I use a table to store the data. The datatypes for for the
two datecolumns are datetime.
When executing the package I always get an conversion
error: "The value could not be converted because of a
potential loss of data". Even after using a dataconvertion
task which converts the two columns into DT_DBTIMESTAMP or
DT_DBDATE
Any help is greatly appreciated.
Q
February 23, 2005 at 8:00 am
This was removed by the editor as SPAM
December 13, 2005 at 10:39 am
I'm having the exact same problem. This can't be that complicated... Any pros out there willing to share their knowledge??
December 13, 2005 at 6:44 pm
I had to do a date conversion from a Sybase integer that was the number of seconds from a "base date" which was defined as January 1, 1970. I added the following two columns to my query in the data source and mapped them accordingly to the output columns:
DATEADD(second, autoDisc, '01/01/1970') AS newAutoDisc,
DATEADD(second, admitDate, '01/01/1970') AS newAdmitDate
Your date conversion function would be different; but the same concept.
December 15, 2005 at 9:58 am
I had the same problem with dates in aaaammmdd format on my flat file.
I solved on this way:
1)The input column has a DT_str datatype input lenght 8, output lenght 10
2) I add a script task in which I extract year, month and day from my string and I recombine them into a data format (dd/mm/aaaa...I'm form Italy)
3) I add a dataconversion task which converts my field from DT_STR into dt_dbtimestamp datatype
4) I insert the field into my table.
in the script task I put the following code (the empty Else is for manage NULL values):
Public
Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'conversione date*********************************
Row.CRSG1DTCONTABILE = ConvToDate(Row.CRSG1DTCONTABILE)
End Sub
Private Function ConvToDate(ByVal DateString As String) As String
'questa funzione trasforma una stringa in una data nel formato gg/mm/aaaa
'se la stringa è vuota viene passato implicitamente il valore NULL
Dim sDay, sMonth, sYear As String
If Trim("" & DateString) <> "" Then
sDay = Mid(DateString, 7, 2)
sMonth = Mid(DateString, 5, 2)
sYear = Mid(DateString, 1, 4)
ConvToDate = sDay &
"/" & sMonth & "/" & sYear
Else
End If
End Function
End class
I hope it's not too late!
bye
Sergio
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply