| Author |
Topic |
|
gaby_58
Starting Member
33 Posts |
 Posted - 2013-11-13 : 15:42:18
|
| 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
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-13 : 16:05:12
|
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 OptimizerTG |
 |
|
|
gaby_58
Starting Member
33 Posts |
Posted - 2013-11-13 : 16:14:07
|
| 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. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-13 : 16:22:40
|
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 OptimizerTG |
|
|
|
gaby_58
Starting Member
33 Posts |
Posted - 2013-11-14 : 09:07:26
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-14 : 09:14:14
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
gaby_58
Starting Member
33 Posts |
Posted - 2013-11-19 : 10:03:09
|
| 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 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-11-19 : 11:11:09
|
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 table2. 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 OptimizerTG |
|
|
|
|
Why this error when trying to import