Data conversion failed. The data conversion for column "TIME PERIOD" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Data Flow Task, Flat File Source [565]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "TIME PERIOD" (590)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "TIME PERIOD" (590)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
0
votes
time period in the CSV file looks like feb 13 ...i have used data conversion task and changed it in to date[DT_TIME] and added columntransfomration to get "20" + RIGHT(" 0" + (DT_WSTR,2)DAY((DT_DBDATE)[TIME PERIOD]),2) + "/ " + RIGHT(" 0" + (DT_WSTR,2)MONTH((DT_DBDATE)[TIME PERIOD]),2) + "/01"
- Kumar
Don't put code into comments like. You can edit your question and add it there
- Marc B
I would guess that you have one or more bad records
- HLGEM
I iam trasfering 3.5k records but i need to transfer nearly 6k records
- Kumar
3.5k rows is about half of your 6k source. That usually smells like an unescaped, embedded column delimiter (an extra comma), an extra row delimiter (crlf, cr or lf depending on your source file) in the data or inconsistent line endings
- billinkc
1 Answers
0
votes
I suggest importing flat files to staging tables (all text fields, no casting) then migrating them to your final table. Importing them without casting will sidestep errors like this, as long as your text fields are sufficiently long as to avoid truncation.
When you migrate the data in SSIS from staging to final table you can direct rows that error to an appropriate error output that you can use to isolate the problematic rows and decide how to handle them. Then you can fix and migrate those rows separately.
To my knowledge there's not an easy way to handle capturing problematic rows when casting on flat-file import in SSIS
You could change your SSIS to not fail the package on error, but then you'd have to dig for the problematic rows in the csv.
