Excel format in SSIS and warning “The number in this cell is formatted as text or preceded by an apostrophe”

0
votes

What is difference between value with warning "The number in this cell is formatted as text or preceded by an apostrophe" and value without this warning? Excel source data

Problem is when the data are imported in SSIS and rows until 2207 are imported correct but from 2208 are just NULL Simple example SSIS diagram and the data from viewer while debuging

I use such ConnectionString:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Filename.xlsm;Extended Properties="EXCEL 12.0 MACRO;HDR=NO;IMEX=2";

It doesn't matter if I use

"IMEX=1" or "IMEX=2"

Columns A..P are (DT_WSTR,255) type.

How to ensure that imported values were the same like in source Excel file (if possible without changing Excel source file)?

1
excelssisformat

1 Answers

0
votes

Try adding a derived column node and convert the columns to text (versus the wstr). Make sure you map the derived column in your output. If text doesn't work try int or numeric.