April 12, 2019 at 3:50 pm
I don't have a lot of expertise with SSIS but when I create a simple package (source "Excel" and dest "SQL Server"), I run into some issue and wondering if I can get some help.
I have an excel sheet (source) with handful of columns but when I preview it, it shows some values as NULL for "Ticket Number" column and I know why because this column has 2 types of data. For example, the column name "Ticket Number" has 2 types of values
1. integar value (68452135121)
2. 8AP97LP1N which shows as a NULL value when I hit preview.
How do I resolve this issue?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 12, 2019 at 4:16 pm
Have you tried converting the column type into a string? I'm just getting this out of memory, but it should be defined in the Excel Source Editor in the Columns or Advanced tab.
April 12, 2019 at 4:28 pm
Tried string, Unicode string but nothing. Still see NULL value
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 16, 2019 at 4:16 pm
I learned this the hard way years ago and had to do a lot of looking to find the answer...
SSIS determines the data type of columns in Excel by reading the first 8 rows (by default). So, if the first 8 rows only have numeric values, it will assume the rest are numeric and change any non-numeric to NULL. Adding IMEX=1 will force it to treat them as text. However, if the first 8 rows have NULL values, it will assume the rest are NULL. You'll need to update the registry so it reads all rows before determining the data type.
Here's a link o a web page that explains it very well:
https://www.concentra.co.uk/blog/why-ssis-always-gets-excel-data-types-wrong-and-how-to-fix-it
April 16, 2019 at 8:26 pm
I write imports for excel in SSIS a lot. To get around this you need to ensure the field in excel is formatted as text and the SQL column your inserting into is nvarchar (basically a string).
mcweber is dead on but if the whole column is in text format this isn't an issue.
I have problems with leading zeros disappearing on me and this is how I get around it.
Excel is a TERRIBLE tool for ETL...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply