Data Conversion Transform Length Limit
Hi Brad Wood, The underlying reason for such issues is the limited number of data types supported by OLE DB Provider for Jet 4.0, which provides the interface between Excel and SQL Server 2008 Integration Services. In particular, content of individual columns of a spreadsheet can be categorized as one of the following SSIS data types as below: Boolean – corresponding to Boolean (DT_BOOL), Currency – corresponding to Currency (DT_CY), Date/Time – corresponding to Date (DT_DATE), Numeric – corresponding to Double-Precision Float (DT_R8), Memo – corresponding to Unicode Text Stream (DT_NTEXT) - if the length of column values exceed 255 characters, String – corresponding to Unicode String (DT_WSTR) - if the length of column values does not exceed 255 characters. For more information about it, please refer to the following article: Importing Excel data into SSIS 2008 using Data Conversion Transformation: http://www.databasejournal.com/features/mssql/article.php/3878291/Importing-Excel-data-into-SSIS-2008-using-Data-Conversion-Transformation.htm Thanks, Eileen Forum Support Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact email@example.com.
October 22nd, 2011 12:15pm
I'm creating packages that get data into a temporary table in SQL Server where checks are run on the data before it goes to it's final destination. Each column in the temporary tables are varchar(8000). Each package contains just one Data Flow Task with a source and a SQL Server Destination. Since I want to allow for maximum width, I'm designating all columns heading into the SQL Server Destination to be of length 8000. When testing flat files, I ran files containing 250 columns with no problem. When importing XML or Excel files, I must run the data through a Data Conversion Transform. The output columns are again of length 8000 (DT_STR). If I run a package with more than 123 columns (regardless of the actual width of the data in the column - test data was a single character in each column), the package just stops somewhere in the transform. A bulk insert statement has begun on the server, but the package does nothing more, and when I kill it, I can see that no data made it to the destination table. What should my strategy be? Is there some known length limit in the Data Conversion Transform that I should be aware of and adjust the total length of all my columns accordingly?
October 22nd, 2011 7:12pm
The underlying reason for such issues is the limited number of data types supported by OLE DB Provider for Jet 4.0, which provides the interface between Excel and SQL Server 2008 Integration Services. In particular, content of individual columns of a spreadsheet can be categorized as one of the following SSIS data types as below... This doesn't seem to have anything to do with my question. My question deals with the halting of executing packages when I add too many columns of a particular type to a data conversion transform. The problem occurs regardless of the source of the data.
November 19th, 2011 12:28pm