June 3, 2008 at 1:26 am
Hi,
I am trying to import data from Fixed Width File to SQL Table but I am facing issue with some of field. As some of fields are in Numeric and Date format I am not able to import these fields to table which have same data type. When I convert all data type to DT_STR and in table make varchar I am not getting any issue.
So
1) Why I am not able to import data when use numeric or date type even though in flat file it's coming like 0, 77777 (for numeric) or 21/12/2008 FOR DATE type?
Please help me..
I am using SP1 Version...
Thanks!
June 3, 2008 at 2:45 am
What happens when you use numeric and date types?
June 3, 2008 at 3:09 am
error :-
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 12" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
June 3, 2008 at 4:55 am
Can you please post what a couple of sample values, and what you are setting the value as in SSIS.
A sample file would be first prize
June 3, 2008 at 7:42 am
Attached Flat File Fixed Width ....
Column name Length
PERSON ID 20
FIRST NAME 30
LAST NAME 30
MIDDLE NAME 1
EMAIL 255
GENDER 1
DAYTIME PHONE 10
EVENING PHONEPHONE10
LOGIN NAME 255
IS LOCKED 1
IS UNSUBSCRIBED 1
UNSUBSCRIBED DATE MM/DD/YYYY
ADDRESS LINE1 60
ADDRESS LINE2 60
CITY 30
STATE 2
ZIP 30
SSN 9
DATE OF BIRTH 10
IS FUNDS TRANSFER SIGNED UP1
FUNDS TRANSFER SIGNED UP DATEMM/DD/YYYY
SEGMENTATION CODE 2
DRIVER LICENSE NUMBER 30
DRIVER LICENSE ISSUED_STATE 2
DRIVER LICENSE STREET_ADDRESS255
DRIVER LICENSE CITY 25
DRIVER LICENSE STATE 2
DRIVER LICENSE ZIP 10
NUMBER OF YEARS AT CURRENT ADDRESS2
FORMER STREET ADDRESS 255
FORMER CITY25
FORMER STATE 2
FORMER ZIP10
AGE OF HOME PHONE INDICATOR 1
HOME PHONE PUBLISHED1
P2P SIGN UP STATUS1
In SSIS screen if you put all in DT_STR it will work but just try DT_NUMERIC for last column or all other column which is numeric and use datetime for all column which is date it won't work..
Thanks!
June 4, 2008 at 12:53 am
The last column cannot be numeric.
P2P SIGN UP STATUS has values of A and E in the content file you supplied
I made the person id 4 bit signed integer
I made the IS LOCKED and IS UNSUBSCRIBED single bit signed integers
I made the UNSUBSCRIBED DATE DT_Date
The first time I ran the SSIS packge, I encountered the same error -
Data conversion failed. The data conversion for column "UNSUBSCRIBED DATE" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
So I changed this to DT_STR(10), and the data loaded to a small test table I created.
The I ran a query on the table as following:
select isdate([UNSUBSCRIBED DATE]) from dbo.test
3 of the values (most probably the blanks) returned a 0 status.
All the integer fields returned integer values correctly.
Not sure if this is the reason, but I suspect so.
Hope this helps
~PD
June 4, 2008 at 1:44 am
Hi PD
Thanks for reply...
(Please see attachement)
1)Regarding last column please put 2002 in row width and you will see last three column will be NN3 and 3 will be P2P SIGN UP STATUS. ACTUALLY WHAT YOU ARE SEEING IS NOT LAST COLUMN .. IT SEEMS TO BE LAST AS IN NOTEPAD BUT THAT IS LAST IN FIRST LINE OF NOTEPAD BUT ROW WILL NOT ENDING THERE BUT IN NEXT LINE YOU WILL SEE 'NN3' VALUE ALSO. First time I also got confused but when I put row width 2002 in column tab I will get proper value.
2) I also used for IS_LOCKED and IS_UNSUBSCRIBE column as 1 byte sign integer and will get following error ... I am not sure why you didn't get... Could yoy let me know how you have done that...
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 7" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Advance thanks...
Note:- I am using SP1 .. Just want to know if this is issue of SP1...
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply