Having issues with Excel source in SSIS

  • Syed_SQLDBA

    SSCertifiable

    Points: 6513

    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]

  • Luis Cazares

    SSC Guru

    Points: 183576

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Syed_SQLDBA

    SSCertifiable

    Points: 6513

    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]

  • mcweber58

    Grasshopper

    Points: 19

    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

     

  • Chris Hurlbut

    SSCarpal Tunnel

    Points: 4052

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply