Null values in dates - unable to read from ssis

Shambhu Rai 1,141 Reputation points

Hi Expert,

I am importing dates from excel to SSIS to load data but some values are null in dates so ssis unable to read it as date and reading as nchar


SSIS reading as number or nchar

have another date dimension where there is no null data and reading properly

expected results as below.Please suggest . I heard about ime=1 but do not know how to import it


SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,635 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,323 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,208 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Michael Taylor 41,811 Reputation points

    There is no such thing as a NULL cell in Excel. An empty cell in Excel is simply an empty string. Excel doesn't actually understand types at all. Everything is a string. What you're referring to as a "type" in Excel is just the formatting that is applied to a column/cell for display purposes and has no bearing on the actual string value. When SSIS imports the data is sees everything as strings. To make the import a little easier it tries to figure out what the actual type is by heuristics. If it cannot figure it out then it just assumes string. Based upon this document it currently uses the first 8 rows. If you have mismatched data then it won't be able to figure things out. Therefore the recommendation is to modify the Extended Properties to include IMEX=1 which tells the provider not to bother and everything gets imported as a string. You would then add a data transformation step to convert the textual values to the actual types you need using the standard SSIS tools. It is at this point you'd also be able to do conditional things like change empty text values to a default value if you wanted.

    0 comments No comments

  2. Shambhu Rai 1,141 Reputation points

    can not find this option in ssis
    Extended Properties to include IMEX=1

  3. Shambhu Rai 1,141 Reputation points

    ALSO tried this syntax in connectionstring
    " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Source1.xlsx;Extended Properties="EXCEL 12 HDR=YES;IMEX = 1 ;MAXSCANROWS = 15;READONLY = FALSE";"

  4. Shambhu Rai 1,141 Reputation points


    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Source1.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";

    again error

  5. Shambhu Rai 1,141 Reputation points

    I have changed the format in excel sheet to date and it works... getting error in converting nWSTR TO Integer gives as error using data conversion function

    while converting column "No 1" (38) to column "Copy of P#O No 1" (6). The conversion returned status value 2 and status text