SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Why this error when trying to import
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gaby_58
Starting Member

30 Posts

Posted - 11/13/2013 :  15:42:18  Show Profile  Reply with Quote
Hi All,

I am getting this error when tried to import a table from Access database to SQL Server, could you please let me know what needs to be done..if I do the Insert in SQL Server Database then it works fine.

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Stlls' with unique index 'IX_Stalls'. The duplicate key value is (<NULL>, <NULL>).".
(SQL Server Import and Export Wizard)

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/13/2013 :  16:05:12  Show Profile  Reply with Quote
quote:
"Cannot insert duplicate key row in object 'dbo.Stlls' with unique index 'IX_Stalls'. The duplicate key value is (<NULL>, <NULL>)."

your target table has a unique index on it. Either the data in your access table contains duplicate rows (at least 2 rows where both values that comprise your index are NULL) or just one row but the table already has a row where both those values are null.

Be One with the Optimizer
TG
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/13/2013 :  16:14:07  Show Profile  Reply with Quote
I didn't see any null values in those fields, also when I do the Insert in SQL Server it works fine, so not sure why this behaviour. Is it a good idea to drop the index and recreate after imporing the data. Thanks for any suggestion.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/13/2013 :  16:22:40  Show Profile  Reply with Quote
Any time I import data I always import into a staging table. I relax all the constraints and sometimes the datatypes to insure that no matter what the data looks like it will get into sql. Then a separate, more controlled process to move the data from the staging table to the real table. All business rules can be enforced in this second process including honoring constraints, data transformations, etc.

EDIT:
quote:
when I do the Insert in SQL Server it works fine

I'm curious what you mean by this. When you "insert in SQL Server" what are you inserting from?

Be One with the Optimizer
TG

Edited by - TG on 11/13/2013 16:26:40
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/14/2013 :  09:07:26  Show Profile  Reply with Quote
I am using the Insert statement to insert these values and it works fine..how do you move from stanging table to the real table. I tried doing that still no luck
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/14/2013 :  09:14:14  Show Profile  Reply with Quote
quote:
Originally posted by gaby_58

I am using the Insert statement to insert these values and it works fine..how do you move from stanging table to the real table. I tried doing that still no luck


just use insert into..select syntax once you've cleansed the data and removed invalid rows as per your validation rules.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gaby_58
Starting Member

30 Posts

Posted - 11/19/2013 :  10:03:09  Show Profile  Reply with Quote
Actually I am able to find these duplicate rows, there are 2 columns and if those 2 columns have same number in both the tables then it is failing. Is there a way to add these saying ignore or something. Thank You for any tips

Edited by - gaby_58 on 11/19/2013 10:18:51
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/19/2013 :  11:11:09  Show Profile  Reply with Quote
No magic button - you'll have to do some actual work
You have 2 options:
1. clean up the source data and do a straight import into the target table
2. import the data (as is) into a staging table and then use a sql process to populate the target table with exactly what you want.

If you go with option 2 then you'll have to know how you want to deal with these duplicates. For instance which row you want to keep. I assume that the non-primary key attributes have different values row to row. you'll need to logically define how to decide which row (or which attribute values) you want to keep.

If you want more specific help then you'll need to ask a more specific question. Ideally post some DDL/DML for us to use to provide an example solution. Be sure to include the primary key of the target table.

You can also search this site for "removing duplicates" to see some other example solutions.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000