Skip to content Skip to sidebar Skip to footer
Home / Etl / Sql Server / Sql Server 2000 / Ssms

Sql Server Management Studio: Import Quietly Ignoring 99.9% Of Data

Post a Comment
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

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

Post a Comment for "Sql Server Management Studio: Import Quietly Ignoring 99.9% Of Data"