SSIS – text values importing as NULLS

Problem: (SSIS) Importing an Excel spreadsheet, a column which contains both numbers and letters. The non numeric value aren being imported as NULL

Solution: 3 Parts, all mandatory

Spreadsheet: Add an apostrohie in the first row of data eg 1 becomes ‘1
SSIS: add IMEX=1 to you excel connection string eg

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\filepath\ssis.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES”;

becomes

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\filepath\ssis.xlsx;Extended Properties=”EXCEL 12.0 XML;HDR=YES;IMEX=1″;

SQL Server: change your destination column type to unicode eg varchar(100) becomes nvarchar(100) alternatively use a data conversion component to change the unicode data to non unicode in your SSIS package

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: