SSIS not importing all of Excel file

  • I've got an Excel source and OleDB Destination set up.

    The SSIS package runs, but I only get 23591 records, out of over 37000, every time. I've looked at the Excel file and there doesn't appear to be any problems at that point in the file. No gaps, no changes in data type, nothing I can see to cause it to blow.

    Could it possibly be the Destination type I set up?

    I even tried creating a package with the Database/Import task. Same problem.

    Any other ideas to check?

    David

  • Try sorting the data in the Excel file differently and see whether it still bombs out at the same place. That should indicate whether it's data or a setting somewhere.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (5/15/2014)


    Try sorting the data in the Excel file differently and see whether it still bombs out at the same place. That should indicate whether it's data or a setting somewhere.

    I sorted differently, and it still bombed with the same number of rows read. What settings might I look at?

  • No errors/warnings?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (5/15/2014)


    No errors/warnings?

    Warning: "Could not open global shared memory to communicat with performance DLL."

    I get this on other things that run fine.

  • I tried using a ADO.Net destination instead, and it give the same result. I tried "ignore failure" on all columns.

    I'm kinda stuck. If anyone has some suggestions on what settings and stuff I can try, I could really use the help.

  • Try copying the first data row down so all the rows have exactly the same data - does it fail then also?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (5/15/2014)


    Try copying the first data row down so all the rows have exactly the same data - does it fail then also?

    Or copy the contents into a brand new Excel file.

    ps: you can ignore the warning about global shared memory

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • You could also try saving the contents of the Excel file to CSV and importing that.

    If it still fails, you'll know that you have to focus your investigations on the target rather than the source.

    Is the target a SQL Server db, by the way?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (5/16/2014)


    You could also try saving the contents of the Excel file to CSV and importing that.

    If it still fails, you'll know that you have to focus your investigations on the target rather than the source.

    Is the target a SQL Server db, by the way?

    Target is SQL Server 2008. Table has no constraints, no "not null" fields. All the fields are 255 nvarchar characters, except the Description field, which is 1000. And two date fields. Longest description value in the incoming file is 240 characters.

    I did convert to a CSV. It gave me a variety of problems. Didn't like the "/" character in some field names, so I changed that. Kept giving me a lot of truncation errors, even though the incoming data was not as long as the field, even taking into account doubling for unicode. I told it to ignore truncation errors, and, sure enough, it truncated the Description field. trying to put 240 characters into a 1000 nvarchar field.

    I finally got it to import, but it pulled int 37,260 out of 37,262 records.

    I also tried creating a SSIS package automatically, by using the [Database]/Tasks/Import Data feature. It added a translation module into the data flow, converting everything to Unicode that required it.

    EDIT:::

    I also just checked the data lengths that got imported, for that description field. 50 characters. Field defined as 1000 nvarchar.

    EDIT2:::

    Here is the table definition:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[tbl_Archive_mHub_AVL](

    [Cco P/N] [nvarchar](255) NULL,

    [Description] [nvarchar](1000) NULL,

    [Mfg Name] [nvarchar](255) NULL,

    [Mfg P/N] [nvarchar](255) NULL,

    [Qual Status] [nvarchar](255) NULL,

    [Change Date] [datetime] NULL,

    [UOM] [nvarchar](255) NULL,

    [cmgr] [nvarchar](255) NULL,

    [Load_Date] [datetime] NULL

    ) ON [PRIMARY]

  • Ok, partial solution.

    The field was truncating because the Source Data Connection for XL had the field defined as 50 char. I guess it did that by default because I didn't set it.

    Still don't know why it's losing two records out of 37262.

  • Do you know by any chance which 2 records it is losing? At the beginning, at the end, somewhere random?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (5/17/2014)


    Do you know by any chance which 2 records it is losing? At the beginning, at the end, somewhere random?

    Not yet. I have to get both the source and the result into a database where I can compare them (and know I have all the records), like Access. Can't just compare on one field in XL as it is a multikey primary key.

  • Well, I never really solved this.

    Or maybe I did. It was not importing all the CSV rows because double quotes within the description field was throwing values into wrong columns.

    I found an MS Access source for the same data, and that works fine.

  • Just for any one else interested, with an ETL issue like this you can add a Data Viewer to the data flow and see the rows thrown at the destination (for example). Right click the green arrow going down and add it there. As it runs you can also copy the data for inserting into excel to do more inspections there if you wish.

    ----------------------------------------------------

  • Viewing 15 posts - 1 through 14 (of 14 total)

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