0

I have a SSIS package which sources from an Excel file, performs a lookup in SQL, and then writes the fields from the lookup to a flat file. For some reason, any of the fields in the SQL table that are of data type varchar 256 are not getting written. They are coming in as nulls. My other fields, including varchar 255, are coming across fine. I have tried flat file and Excel as destination with no luck.

I've tried converting the varchar with a data conversion to both 256 and to a Unicode string and no luck.

Even when I preview a simple query in the source component (ex: select lastname from xyz), the preview shows the lastname as null. It doesnt show other fields that have different data types as nulls.

Natalie Price
  • 101
  • 1
  • The null fields are not part of the Excel file. I only have one field in the Excel file. Using the value of that field, I am doing a lookup to return all fields from the SQL table. When I try to write those fields back to a flat file, they are coming across as nulls. – Natalie Price Jul 30 '19 at 23:24

1 Answers1

0

This is usually a case when the excel driver only reads the first 8 rows of data and misinterprets the correct data type because of the lack of data it's checking. Here are some of the known issues from the Microsoft site: Reference

Issues with importing

Empty rows

When you specify a worksheet or a named range as the source, the driver reads the contiguous block of cells starting with the first non-empty cell in the upper-left corner of the worksheet or range. As a result, your data doesn't have to start in row 1, but you can't have empty rows in the source data. For example, you can't have an empty row between the column headers and the data rows, or a title followed by empty rows at the top of the worksheet.

If there are empty rows above your data, you can't query the data as a worksheet. In Excel, you have to select your range of data and assign a name to the range, and then query the named range instead of the worksheet.

Missing values

The Excel driver reads a certain number of rows (by default, eight rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination.

You can modify this behavior of the Excel driver by specifying Import Mode to import all values as text. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

Truncated text

When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated.

To import data from a memo column without truncation, you have two options:

Make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters

Increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the following registry key:

Redistributable components version - Registry key

Excel 2016 - HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel Excel 2010 - HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Aaron Cannon
  • 71
  • 7