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.

Can we please get the Enterprise Manager dev team to re-write SSMS?

---

It's safe to say that 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.

Edit: Reading my old question on ServerFault, about SSMS silently not scripting some tables in a database, i had at the end, "Honk if you hate SSMS". So:

Honk if you hate SSMS.

The Game

i've created a ticket on Microsoft Connect. Place your bets on when this issue will be Closed as not reproducible. Edit this question adding your name and date. The one closest without going over will win 100 rep! (i will donate my rep) If you don't have enough rep to edit questions, place your guess in a comment, and i'll add it.

The Guesses

• Ian Boyd: 5/1/2011

Update: It was closed 3/18/2011. Off by 43 days.

Answer 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.