November 7, 2007 at 9:00 am
I am very new to SQL Server 2005, and I am currently trying to migrate a 2000 db to 2005.
I have now migrated all the DTS to SSIS, removing any legacy & replacing with new functionality.
I have been through a group of related packages ironing out any problems. They ALL now run fine when called individually.
However, I am now having problems with the parent package that is used to string these all together. It will not even run the 1st package.
For info, the first task of this package is a "Transfer SQL Server Objects Task". I am copying a few tables with data, but WITHOUT indexes, etc.
Following this, there is an "Execute SQL Task" that creates an index on one of those copied tables.
To get past another issue I had to set the "CopySchema" property of the "Transfer..." task to true, but did not select any schemas to transfer.
Now, when running the PARENT package, it is failing at the 1st step of the 1st package as follows:
Error: 0xC002F325 at Copy LT Data Tables, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Hidden Hearing Limited$Telemarketer' with unique index 'IX_Hidden Hearing Limited$Telemarketer1'.".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
I have been searching and searching, finding similar problems, and even the same error codes, but nothing quite matching this particular scenario.
I have been trying to set the "DelayValidation" at various levels, but with no luck.
I would greatly appreciate any help in trying to resolve this. Has anyone else had the same, or similar, issue?
Many Thanks,
Simon
November 7, 2007 at 2:21 pm
I haven't used the Copy Schema or even the Transfer... tasks, but it sure looks like your error is caused by exactly what it's saying...a duplicate key entry. Is there a resulting table from this process that you can look at? Are there options you can use to not copy indexes with the schema for the table? How about figuring out a different way to fix the problem that caused you to use that functionality?
Just trying to offer some kind of help since no one has replied to your post.
Rick
Rick Todd
November 7, 2007 at 3:15 pm
Well I can now officially confirm that I am wearing the "Dunce" cap today!!!
I originally worked on all the packages individually, importing them to SQL Server as I went. After realising that I was missing functionality in BIDS, I then grouped them all together in projects & a solution.
The "Execute Package Tasks" were still pointing to the packages stored on SQL Server. To test all I then did was reimport the packages to SQL Server from the project's bin folder. I have now run the parent package via BIDS, and it has completed successfully.
I suppose that I had best read up on deployment and decide on the best or most appropriate way to go forward with this.
Thanks,
Simon
November 7, 2007 at 3:18 pm
BTW, the "duplicate key" error being returned was due to the fact that the package being referenced had the "CopySchema" property of the "Transfer..." task set to false. This had been changed in the updated one.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply