Excel Truncating Length Ssis
SymptomsWhen you retrieve the external data from an Excel Workbook within an SSIS package, fields that have more than 255 characters will be truncated to 255 characters. In case of SSIS, theexternal columns of the Excel Source component will be limited to a maximum length of 255 characters if the first 8 rows of the Excel Workbook contain fields with a length less or equal than 255 characters.
CauseYour data may be truncated to 255 characters if the first 8 rows in the Excel Workbook for the field(s) being truncated contain 255 or fewer characters. The Microsoft Excel ODBC driver will, by default,scan the first 8 rows of your data to determine the type of data in each column. How to combine mp4 files windows 10. If any of the rows after the first 8 rows of the Excel Workbook contain field(s) having lengths greater than 255 characters these will get truncated.Figure 1In the example represented in Figure 1 the Description field has 255 characters or fewer in the first 8 rows.
Importing from an MS Excel worksheet. To be able to import the data from the Excel worksheet into the SQL database, the user first needs to define the Data Flow Task, dragging from the SSIS Toolbox into the Control Flow area like shown in the image below. Double click on Excel source will open the connection manager settings and provides an option to select the table holding the source data. Here, we are selecting the Employees.xls file present in the file system.
As the Microsoft Excel ODBC driver, by default, scans only the first 8 rows of the Excel Workbook willdefine the metadata for the Description External Column to be of type DTWSTR having a length of 255 characters. However in the 9th row the Description value has a length of 1000 characters and it willbe truncated.
The Excel Source component will not fail but it will truncate the value of the Description field in the 9th row to 255 characters. ResolutionTo change the number of rows that the Excel ODBC driver scans to determine what type of data you have in your table, change the setting of the TypeGuessRows DWORD value.
Excel Truncating Length Ssis Error
NOTE: The registry keys below were taken from 64-bit machine.