March 10, 2010 at 2:39 pm
Hi,
This is driving me crazy. I'm unable to move data from two different databases (same tables, same schema) if I select import. If I try the other way, exporting from the database or table with source data, it works with no issues.
Here's the table structure
CREATE TABLE [dbo].[toys](
[ToyID] [int] NOT NULL,
[ToyDesc] [varchar](20) NOT NULL,
CONSTRAINT [PK_Toys] PRIMARY KEY CLUSTERED
(
[ToyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[orders](
[ToyOrder] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[ToyID] [int] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ToyOrder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orders] WITH CHECK ADD CONSTRAINT [fk_orders_toys] FOREIGN KEY([ToyID])
REFERENCES [dbo].[toys] ([ToyID])
GO
ALTER TABLE [dbo].[orders] CHECK CONSTRAINT [fk_orders_toys]
GO
Here's the error
- Copying to [dbo].[orders] (Error)
Messages
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: "Violation of PRIMARY KEY constraint 'PK_Toys'. Cannot insert duplicate key in object 'dbo.toys'.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (83)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (83)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination 1 - toys" (70) failed with error code 0xC0209029 while processing input "Destination Input" (83). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
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: "Violation of PRIMARY KEY constraint 'PK_Orders'. Cannot insert duplicate key in object 'dbo.orders'.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (38)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (38)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - orders" (25) failed with error code 0xC0209029 while processing input "Destination Input" (38). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Yes, I read what it says 🙂 ... there is a Primary Key insert violation, but why it works the other way, when I select export.
To execute or run the Wizard, I'm using a SQL login with read/write permissions on both tables, which of course, exist on both databases. Can someone please provide some input an explain this odd behavior?
By the way, on the column mapping I'm selecting "append rows" and "enable identity insert", for both tables ...
Thanks in advance,
March 11, 2010 at 7:05 am
bump!
no ideas, have someone seen this problem before? ...
March 11, 2010 at 12:31 pm
My idea ...
If export works, use that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 11, 2010 at 2:55 pm
Phil Parkin (3/11/2010)
My idea ...If export works, use that.
Well, that does not provide an explanation to the main problem I think. If not fixed or unmasked, it could be worse later or start affecting future export operations.
Thanks for reply though ...
March 12, 2010 at 2:15 am
Perhaps if I explain my thinking ...
Here is how I read your post:
"I have a problem and two potential solutions. One solution works and the other does not. Can you tell me why?"
I chose to answer a slightly different question: "Can you suggest how should I proceed?"
At no point was I suggesting that you just go ahead and implement a solution without due testing, to include counters/hash totals/checksums and whatever else you need to assure yourself that your 'working' solution is, in fact, working.
But if I come across that situation, and neither solution is 'better' than the other, I generally just shrug my shoulders and go with the one that works - I am just too busy to chase down all possible solutions, though there is definitely value in doing that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 12, 2010 at 4:13 am
I think You was lucky when export or import succeeded.
Wizard creates one DataFlowTask with two sources and destinations (same for export and import). Sources work parallel and sometimes "orders" are "faster" than toys.
Didn’t try, but If You have 100.000 "toys" and only 1 "order" with last toy from toys, job will always fail.
March 12, 2010 at 5:08 am
Wizard creates one DataFlowTask with two sources and destinations (same for export and import). Sources work parallel and sometimes "orders" are "faster" than toys.
so you simply need a constraint linking the destination of the one you want to run first to the source of the second (use BIDS/Visual Studio click on destination and drag the green arrow to the next source).
Make sure that whatever is referenced as a lookup table is loaded first so that the second table load can look up values in the first table.
March 12, 2010 at 5:58 am
P Jones (3/12/2010)
Wizard creates one DataFlowTask with two sources and destinations (same for export and import). Sources work parallel and sometimes "orders" are "faster" than toys.
so you simply need a constraint linking the destination of the one you want to run first to the source of the second (use BIDS/Visual Studio click on destination and drag the green arrow to the next source).
Make sure that whatever is referenced as a lookup table is loaded first so that the second table load can look up values in the first table.
I am talking about package created by Import/Export Wizard in Management studio (at the end of wizard you can save package and see what wizard do). Copying two tables makes one dataflow task with two sources.
All that means: never use import/export wizard when tables have FK.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply