Alex Rivera | Logout

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

Asked 2010-09-10T19:58:43.217
15

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:

enter image description here

Source is the ContosoFrobManager database on lithium:

enter image description here

Destination is the Grob database on lithium;

enter image description here

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

alt text

i want to copy the STRTransactions table: alt text

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): alt text

Run immediately, that's fine: sql-server ssms etl sql-server-2000

Edit
Report

2 Answers

6

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.

answered 2010-09-10T20:14:54.977
1

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.

answered 2010-12-22T20:03:34.807

Your Answer