Null values in dates - unable to read from ssis

Shambhu Rai 1,411

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

7 answers

Michael Taylor 54,321 Reputation points

2022-03-30T20:28:32.777+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shambhu Rai 1,411 Reputation points

2022-03-30T20:51:32.803+00:00

can not find this option in ssis
Extended Properties to include IMEX=1
Please sign in to rate this answer.
Michael Taylor 54,321 Reputation points

2022-03-30T20:57:27.013+00:00

It is on your Excel connection string. After the source you added an Extended Properties key with the value set to whatever you need (it might already exist) and then include IMEX=1.

Here's an example.

Shambhu Rai 1,411 Reputation points

2022-03-30T21:10:53.493+00:00

here is my excel connection manager an property can not find anything
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shambhu Rai 1,411 Reputation points

2022-03-30T21:15:40.567+00:00

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";"
Please sign in to rate this answer.
Michael Taylor 54,321 Reputation points

2022-03-30T21:22:39.593+00:00

The connstring looks reasonable. So when you used that then the provider should have treated everything as a string on import. Did you then add some data transform task(s) to convert the columns from strings to the format you needed. That is a necessary step in this approach.

Shambhu Rai 1,411 Reputation points

2022-03-30T21:31:26.757+00:00

this gives me an error
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Source1.xlsx; Extended Properties=EXCEL 12 HDR=YES;IMEX = 1 ;MAXSCANROWS = 15;READONLY = FALSE

Michael Taylor 54,321 Reputation points

2022-03-30T21:36:32.863+00:00

The connection string you posted the second time doesn't line up with the first one. This is from the docs.

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

Notice that the provider is ACE.OLEDB, not JET. Also notice the extended properties value is wrapped in double quotes because that entire value is the extended property. But note that Excel 12.0 should match your version of Excel. This is just an example.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shambhu Rai 1,411 Reputation points

2022-03-30T21:45:54.047+00:00

added

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

again error
Please sign in to rate this answer.
Michael Taylor 54,321 Reputation points

2022-03-30T21:58:38.577+00:00

Wait a second. You need to set this on the connection string, not the file path in the UI. The UI you showed is just asking for the filename. That is where you specify the XLSX file. But you need to set the values on the actual connection string, of which this UI is only showing you a subset. To get to that you'll need to view the properties of the connection manager (not the UI you're showing).

This blog article has a screenshot of what I'm talking about. On the ConnectionString property is the Provider=... stuff (which this UI partially sets). You need to add the extended properties to that value.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Shambhu Rai 1,411 Reputation points

2022-03-30T22:23:09.537+00:00

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
Please sign in to rate this answer.
Michael Taylor 54,321 Reputation points

2022-03-31T14:18:05.393+00:00

Trying to convert COPY of P#O No 1 to an int isn't going to work. You need to treat it as a string when importing and in SSIS check to see if the value is convertible to an int or handle the error.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Null values in dates - unable to read from ssis

7 answers

Your answer