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 |
|
ravl13
Starting Member
38 Posts |
 Posted - 2011-09-19 : 15:45:10
|
| Greetings, I'm trying to Import a flat .CSV file into Sql Server 2008 using the Import/Export data wizard, but I get the following error messages when I actually try to execute:"- Executing (Error)MessagesError 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 3" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task 1: The "output column "Column 3" (22)" failed because truncation occurred, and the truncation row disposition on "output column "Column 3" (22)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "E:\raiser edge outputs\PRIMPHON.CSV" on data row 609. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - PRIMPHON_CSV" (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)" Judging from the error messages, Column 3 is causing problems because the data is too long, and would be truncated. I had set column 3, which holds street addresses, to be imported into the table as a column with varchar length(900). If it's longer than 900, it's bad data, so I want to truncate the data so that it fits when I import it into the table.Problem is, I have no idea how to truncate data when importing. How can I do that with the import/export wizard? Or alternatively, what would a script look like that would import the .CSV with truncation enabled? (I know nothing of scripting import statements)**NOTE: the table that I am attempting to export the CSV into is created "on the fly" by the wizard.Thanks in advance,-ravl13 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-20 : 02:46:54
|
| you cant truncate data inside import export task . for that you need to use Integration services package. Create a ssis package in business intelligence studio with data flow task having excel source to connect to csv and oldeb destination to dump data to table. in between place a derived column task and inside it write an expression to replace column3 as subtring(Column 3,1,900) and then use that column for loading to destination instead of actual column3------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
Truncating data when using import/export wizard