SSIS not importing all of Excel file

Question

SSIS not importing all of Excel file

nonghead-webspam

SSCommitted

Points: 1935
More actions
May 15, 2014 at 10:52 am

#307951

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
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]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 2

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?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

No errors/warnings?

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

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 4

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.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 5

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.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

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);

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 9

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]

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 10

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.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

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

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 12

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.

nonghead-webspam SSCommitted Points: 1935 More actions · Answer 13

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.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

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.

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