I'm trying to import data from excel file to sql server. It copies some rows and than get this error messages:
* Error 0xc020901c: Data Flow Task 1: There was an error with output column "Subject_Name" (21) on output "Excel Source Output" (9). The column status returned was: "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 "Subject_Name" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Subject_Name" (21)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
* Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. 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)
I tried to set all the datatype to varchar(max) and to ignore fail on truncation, but i get the same error. Please any help.
Error seems to be due to fact that datatypes of Subject_Name column is not same between source and destination. See whats the datatype it assumes in metadata from Excel. You need to use same datatype for destination or need to apply a derived column/data conversion transform in between to make them same.
More specific? I thought I've already suggested what exactly you need to do. These are the steps
1. Select the excel metadata (click on green line from excel source) and check the data type for Subject_Name column 2. See your destination table's corresponding columns datatype 3. Make then same if possible by changing datatype of table. If you cant change table, add a derived column tranformation or data conversion tranformation in between excel source and table and add an expression to cast the data type of Subject_Name source column to required type.