SQL Server Management Studio: Import quietly ignoring 99.9% of data

Question

The Problem

i'm trying to import data into a table using SQL Server Management Studio's Import Data task. It only brings in 26 rows, out of the original 49,325. (Edit: That's where 99.9% comes from: (1-26/49325)*100 = 99.9%

Using DTS in Enterprise Manager correctly brings all 49,325 rows.

Why is SSMS not importing all rows, reporting that it transferred 49,325 successfully, and experienced no errors? Why is Enterprise Manager able to correctly import all 49,325 rows?

Microsoft SQL Server Management Studio version: 10.0.1600.22 (From SQL Server 2008, installed today on a fresh Windows 7 machine, SP1 applied)

Proof - Import using SSMS

The STRTransactions table is initially empty:

Source is the ContosoFrobManager database on lithium:

Destination is the Grob database on lithium;

i want to copy data from one (or more) tables:

i want to copy the STRTransactions table:

You can append to the existing table, that's fine (it's empty). i want to enable identity inserts. And don't try to import a timestamp (since you'll just complain anyway):

Run immediately, that's fine:

Yup, you're going to do stuff:

i managed to catch it while it was transferring the 49,325 rows, around the 1k mark:

All done. All 49,325 rows copied successfully:

And here's the report:

The execution was successful

• Initializing Data Flow Task (Success)

• Initializing Connections (Success)

• Setting SQL Command (Success)

• Setting Source Connection (Success)

• Setting Destination Connection (Success)

• Validating (Success) Messages Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console. (SQL Server Import and Export Wizard) Warning 0x80047076: Data Flow Task 1: The output column "timestamp" (158) on output "OLE DB Source Output" (11) and component "Source - STRTransactions" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. (SQL Server Import and Export Wizard)

• Prepare for Execute (Success)

• Pre-execute (Success)

• Executing (Success)

• Copying to [dbo].[STRTransactions] (Success) 49325 rows transferred

Messages Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "component "Destination - STRTransactions" (163)" has started. (SQL Server Import and Export Wizard) Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "component "Destination - STRTransactions" (163)" has ended. (SQL Server Import and Export Wizard)

• Post-execute (Success) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - STRTransactions" (163)" wrote 49325 rows. (SQL Server Import and Export Wizard)

Excellent. All done. "wrote 49325 rows".

Except that it only wrote 26 rows:

Obviously i'm not crazy. i did everything right. And even if i didn't, SSMS gives no indication of any problems. i've repeated these same steps 8 times:

• 5 times for myself
• twice to demonstrate to two different colleagues
• once to screenshot the process for this SO question

Every time it's exactly 26 rows, no more, no less. But which was destroyed, the master, or the aprentice?

But just to prove that i'm not doing anything wrong, we'll try again with Enterprise Manager. An excellent tool written over ten years ago:

Proof - Import using Enterprise Manager

i've deleted the 26 rows from the STRTransactions table. i could provide a screenshot that i'm starting with an empty table; or you could just trust me on this. And since the wizards are nearly identical, you'll be seeing nearly identical screenshots. Sorry about that; but nobody will believe me without proof.

i want to export from the ContosoFrobManager database on lithium:

i want to import to the Grob database on lithium:

i want to copy tables:

i want to copy the STR Transactions table:

You can go ahead and append rows to the existing table (it's empty anyway). i want you to insert identity values. And don't try to insert timestamp values, you'll just throw an error anyway:

Run now, sure:

Yup, you're about to do stuff:

i managed to catch it in the middle of the import, around 12k rows:

All done, 49,325 rows successfully copied:

And we select from the table to see the rows:

---

Why is SSMS, a tool which has been actively developed for 6 years now still not gotten in right? While Enterprise Manager was nearly bug-free by the initial dev team? This is another example of the critical bugs in SSMS. The last critical bug I found was that it does not script all objects.

---

I suspect the only answers I'll see are

• use a different tool
• have you reported it on Microsoft Connect?
• call PSS

Of course I have a workaround: stop using SQL Server Management Studio. But I'm already having to run Enterprise Manager in an XP Mode 32-bit compatibility thing (as you can see by the Luna themed windows on my Aero themed desktop)

Me: I got it to work Colleague: How? Me: I used Enterprise Manager Colleague: scoff Well. Colleague: I might have to install that, then.

I created a ticket on Microsoft Connect, but it was closed after several weeks.

Solution 1

The answer:

1. Get a gun.
2. Track down those responsible and ...
3. Just kidding. But someone needs to stand up and take responsibility for their garbage, don't you think? We wouldn't really shoot them, but don't we wish sometimes that we could get face-to-face with the person or team and demand they answer why they did such a bad job?!?!

I agree there is some real junk in the latest Microsoft products. In SSRS when you click into a text box in the middle of existing text and hit paste, after the paste operation the cursor is at the end of all the text instead of at the end of the pasted text (in the middle). SSRS and SSIS are just rife with all sorts of nonsense like this.

Solution 2

I had several such issues. That wizard is absolutely not reliable. If this is a one time task, I would export into csv, and then import from csv. If you need to run it regularly, write your own code.We recently had a similar discussion: Should programmers use SSIS, and if so, why?

Solution 3

There are ONLY two ways that I've been able to get SSIS to do anything correctly.
The first is to bring the data into a brand new table.
The second is to import it into MS Access first then import it from there into sql server.

Quite frankly I have been unhappy since they got rid of DTS. At least DTS worked consistently.

See my Microsoft Connect bug on this.

https://connect.microsoft.com/SQLServer/feedback/details/386948/simple-ssis-import-from-excel-2007-consistently-fails-with-useless-error-messages

They claim the issues will be resolved in the next Sql Server release. Quite frankly, unless they fired the entire team responsible for this feature, I don't believe them.

Solution 4

It seems the same problem just occured to me. Unable to track down the real issue. I don't know if this helps you, it is possible to have problems with PKs and identity inserts.

Quoted from link: ""Enable identity insert" also ignored in certain circumstances"

"export wizard "skipping" records that should be exported"

"Enable identity insert is ignored when "optimize for multiple tables" is enabled. Unfortunately that option ensures that the import operation observes referential integrity between foreign key connected tables"

http://connect.microsoft.com/SQLServer/feedback/details/135905/mappings-settings-not-working-in-export-data-wizard

Very weird and annoying. Are these edge cases - just because SSIS itself is used in quiet big projects/DBs, and I've never encountered this before.

Solution 5

I had a similar issue, only 291 rows transferred from 540K rows, yet the import wizard was showing success (I was going from SQL Server 2008 to 2005).

The solution: I changed the data source:

• from: SQL Server Native Client 10.0
• to: Microsoft OLE DB Provider for SQL Server

on both the source and destination (within the import wizard) and then it worked.

As an aside: If I understand your screen shots correctly, you were also using SQL Server Native Client 10.0 within SSIS import wizard when it failed, yet you were using to Microsoft OLE DB Provider for SQL Server when using Enterprise Manager when it worked.

I have not had any issues using SQL Server Native Client in the past, so I suspect that changing to Microsoft OLE DB Provider for SQL Server was required in my case due to copying data from SQL Server 2008 to 2005.

You did a great job on the screen shots and your question really helped me - so thanks :)

View more solutions