Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vali1005
Starting Member
6 Posts |
 Posted - 2008-04-04 : 15:00:28
|
I have a flat file from which I am attempting to import a column that contains either float numbers or " "(single blank).I get the following Report: quote:
- Executing (Error) Messages * Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "ADR_SH_PER_ADR " returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard) * Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "ADR_SH_PER_ADR " (438)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "ADR_SH_PER_ADR " (438)" 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. (SQL Server Import and Export Wizard) * Error 0xc0202092: Data Flow Task: An error occurred while processing file "F:\WorkVal\Master_Reference_Database\hs_1.txt" on data row 2. (SQL Server Import and Export Wizard) * Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - hs_1_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) * Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard) * Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. (SQL Server Import and Export Wizard) * Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)
Now, the strange thing is, as soon as I import the same column from an Excel file in which, for simplicity of "text to Excel" transfer I have all the columns defined as "text"(I have 170 columns), the import works just fine. The Excel file is just a straight out import into Excel of the flat file.The only difference I see between the flat file and the Excel file is that an empty value in the flat file contains a single blank, while an empty "cell" in Excel contains nothing(cursor doesn't go to the right after clicking inside the cell). By the way, the column in the SQL table is nullable, which is why I thought there should be no issues from an import value containing blanks exclusively. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2008-04-05 : 00:37:25
|
| Are you using the Import Wizard in SSMS? If so then use the Advanced tab to manually edit the type of the ADR_SH_PER_ADR column. It is probably using a type that is too small to hold that imported value. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-05 : 05:57:25
|
| Or try using bcp/BULK INSERT for the import specifying a format file.http://msdn2.microsoft.com/en-us/library/ms178129.aspx |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-05 : 22:10:17
|
| What's data type of ADR_SH_PER_ADR column in sql table? Numeric type will not accept blank character. |
|
|
|
vali1005
Starting Member
6 Posts |
Posted - 2008-04-07 : 11:50:30
|
quote:
Originally posted by snSQL
Are you using the Import Wizard in SSMS? If so then use the Advanced tab to manually edit the type of the ADR_SH_PER_ADR column. It is probably using a type that is too small to hold that imported value.
I figured that the Import Wizard did not like blanks, both from the text file as well as when found inside an Excel cell.And I did try setting the type of the "source" ADR_SH_PER_ADR as "float", but I still got the error when using the flat file.quote:
Originally posted by rmiao
What's data type of ADR_SH_PER_ADR column in sql table? Numeric type will not accept blank character.
ADR_SH_PER_ADR is defined as "float", and it didn't work even when the "source" value was "||"(where "|" is the column delimiter), in the flat file. As I was saying, the strange thing is that Excel does import empty cells as NULL values in ADR_SH_PER_ADR column.My next step is to actually "massage" the Excel file and convert everything I can to "empty" cells. When I will be able to create packages in Integration Services, I will work on modifying them to take into account all the various values in the flat file that should be converted to NULL. |
|
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-04-08 : 10:55:15
|
| Hi Vali, i could read your whole post but if the problem is that you are trying to import a null column from a flat file and you get an error eventhough your database field is set to nullable; if you are trying it in SSIS i can give u a small tip. Try importing the field like a string and in a derived column create trhis expression for the field:LEN(TRIM(Column_Name)) > 1 ? (DT_I4)Column_Name : 0It will convert any null value to 0 or the desired value, could be "Null" if you want.The problem is you cant directly importar a blank or null column from a flat file to an Numeric Column in SSIS since it wont read the null as an integer, that the reason why you need to convert it. |
|
|
|
c000001
Starting Member
2 Posts |
Posted - 2009-11-23 : 04:06:18
|
| Thank you LoudBliss....works perfectly |
|
|
|
erwee
Starting Member
1 Post |
Posted - 2010-06-03 : 13:12:52
|
quote:
Originally posted by loudbliss
Hi Vali, i could read your whole post but if the problem is that you are trying to import a null column from a flat file and you get an error eventhough your database field is set to nullable; if you are trying it in SSIS i can give u a small tip. Try importing the field like a string and in a derived column create trhis expression for the field:LEN(TRIM(Column_Name)) > 1 ? (DT_I4)Column_Name : 0It will convert any null value to 0 or the desired value, could be "Null" if you want.The problem is you cant directly importar a blank or null column from a flat file to an Numeric Column in SSIS since it wont read the null as an integer, that the reason why you need to convert it.
Thanks for that . The formula :LEN(TRIM(Column_Name)) > 1 ? (DT_I4)Column_Name : 0works perfectly, assigning 0 to my FLOAT columns value.Since im not that great with these formulas, can you pls tell me how i can change it to assign a NULL value to the float columns instead.Cheers |
|
|
|
|
|
|
|
|
SSIS - Flat file error on "float" column