November 19, 2007 at 3:46 pm
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]
November 20, 2007 at 5:49 am
Try using DT_DBDATE instead. I have had better luck with it.
November 20, 2007 at 7:30 am
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]
November 20, 2007 at 8:17 am
😀 -----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]
November 20, 2007 at 10:24 am
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.
July 29, 2009 at 2:10 pm
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
April 7, 2010 at 3:51 am
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.
March 26, 2014 at 7:20 am
This is a common thing. The default in a lot of cases for imports into ssis from another type of system where metadata for columns cannot be determined is to default to str(50). Since you are trying to push that into a one character column, it assumes that you may lose data. Simply go into the source component by right clicking and choosing "Show Advanced editor..."
Then navigate to the last tab (Input and Output Properties)
Click the + next to OLE DB Source Output
Click the + next to Output Columns
Highlight the ID column
Scroll to the Length Data Type Property on the right of the dialog box and change it from 50 to 1.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply