Conversion Fail-Potential Data Loss

Question

Post reply

Conversion Fail-Potential Data Loss

Lookup_BI

SSCrazy

Points: 2964
More actions
November 19, 2007 at 3:46 pm

#153972

Hi,
I have a table with column [Date],data type varchar(50) in the format of 'mm/dd/yyyy'.
This data is coming in from a txt file into a table(A) by the means of an existing SSIS package.
I need to load Date records from table(A) to table(B).
Table(B) spec is :-
Columnname =Date
Datatype=datetime
Format/Style='mm/dd/yyyy'
I have tried creating an SSIS package as below!
Source(Table A) > Data conversion [DT_Date] >Destination(Table B)
It fails with error "[Data Conversion [1577]] Error: Data conversion failed while converting column "Date" (123) to column "Copy of Date" (1590). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". .
Do let me know where I am going wrong ?Thank YOU !
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
March 26, 2014 at 7:20 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jere42 Old Hand Points: 305 More actions · Answer 1

Try using DT_DBDATE instead. I have had better luck with it.

Lookup_BI SSCrazy Points: 2964 More actions · Answer 2

I have tried converting the column to

DT_DBDate / DT_Date / Dt_DBTimestamp.But no luck !

This is an example of the value of column Date in Table A > '11/20/2007' [varchar(50)]

I am not able to move ahead with my SSIS package as I need this record to be inserted into Table B, datetime data type .

Someone outhere with any IDEAS !:crazy:

[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

Lookup_BI SSCrazy Points: 2964 More actions · Answer 3

😀 -----SOLVED !

Hi ,

I wrote a sql query to replace Date records which are NULL and empty to a

default value.And then performed my data conversion on DT_Date in data conversion transformation.

WIERD !

Even when 'retain NULL values' was selected in previous package to dowload records from txt file to table(A).Empty records were not coming as NULL.

I'm still rather :ermm: why this has happend ?....Thank You !

[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]

Powichrowski Marek Hall of Fame Points: 3483 More actions · Answer 4

When accessing data from txt files then the best way to manage this data is use Derived Component straight from source and explicitly give proper type for each column from the source.

vermarajiv SSC Enthusiast Points: 162 More actions · Answer 5

I had a similar problem with dates going from CSV to SQL table. I found that it was blank values in csv file that was giving me this error. By creating a derived field of type DT_DATE and replacing the blank with default value like "1/1/1900" did the trick for me. My expression in the derived field looked like this

LTRIM(["birthDate"]) == "" ? "1/1/1900" : ["birthDate"]

I then mapped this derived field to the datetime column of the table.

Hopefully this helps.

RK

chivuklula srikanth SSC Journeyman Points: 87 More actions · Answer 6

While configuring SSIS package please right click on SSIS's destination > edit > error output > on error select ignore failure and in "set this value to selected cells" drop down also select the same "ignore failure" > apply > ok

After that run the ssis package and the file will be imported.

I believe this will work fine. Please let us know it works or not.