Import & Export Wizard...issue
-
This is a little long so I put cliffs at the end for those who hate reading long boring rants like mine...
I have had a couple of posts about a problem I had in general dealing with a collation problem during a upgrade from 2000 to 2005. Basically I had restored a 2000 database on a 2005 box which had the wrong collation (I didn't check initially cause I thought it was the same) during an upgrade I was roped into.
So this was discovered after the install and my initial plan was going to be like I usually did in 2000. Script out all the objects in the database and do an import/export of all the data from the old (incorrect) database to the newly scripted database (right name and collation)...easy enough right.
I initially renamed the old database to something like Database_TEMP and left the new databases name intact. So far everything was working great, the old database was renamed, the new database was created and matched up object for object.
Now came the nice F.U. from the integration services import wizard. I do the basic noob import wizard, select the source and destination...in the options I check the IDENTITY_INSERT option (cause some of the tables had identity columns), and the optimize for many tables option.
click the finish button and everything looks like it is going to work until I started getting this error on random tables...
Copying to [dbo].[EngineeringUnit] (Error)
Messages
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (44)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (47)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (44)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (47)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (44)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server&LinkId=20476
ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of "output column "ErrorCode" (31)" and "input column "ErrorCode" (47)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Which bombed out the wizard and screwed up everything...the funny (actually REALLY irritating) part is there was no column in the database that had a name of "output column" so I am guessing that is something to do with SSIS...
And true to Microsoft...the link that is listed for "more help" when to nowhere...which made me laugh and cry at the same time. I would like to think that in this day and age they could have had SOME sort of error message that was descriptive enough to be able to be troubleshooted or researched...but I guess I am either wrong or am missing the point of the above error messages cause they are complete jibber jabber to me.
After numerous attempts with the wizard and different options being checked and getting nowhere I finally created a script that built the INSERT/SELECT statements to copy each table...
I haven't had any formal training unfortunately through work on SQL 2005 so it has been a learning by the seat of my pants type of experience, but have worked in the 2000 environment for sometime now and I must say I for one am very unhappy with the way 2005 is setup and that the options that are available in 2000 are no longer available in 2005 on top of the repetitive steps you have to go through to do mundane tasks like deleting something.
I am not sure if it was the specific server I was on, but as an example to script out all the database objects in 2005 (roughly 650+) took over a half hour to generate the script...vs. what I would think would have taken less than a minute in SQL 2000.
I just had to rant a little bit because I am the only one at work that deals with SQL 2005 so far and no one else could understand my irritation
...CLIFFS
1. installed 2005 and restored a 2000 database with wrong collation (never checked but you can sure bet I will now)
2. attempted to script out database to recreate new database and import all data
3. import wizard was giving me error after error which meant nothing and I couldn't figure it out
4. built SQL script to generate the code to do the import manually for each table
5. ran homemade script and it completed in less than 5 minutes and I was back in business...
Moral of the story for me is to never rely on Microsoft's wizards for anything again
Leeland
-
some questions to think about:
1. Is the old database still up? Did you run the migration tool on it?
2. Don't the subscripts imply position in a column list? Is it possible that there are duplicate names for other than PK/FK pairings? Is it possible that the PK/FK pairing is being 'misunderstood' because of naming shortcuts that are confusing SSIS?
I believe that 2005 does distinguish between upper and lower case - i haven't run the migration tool for a couple of months now, but i remember that 'generic' warning.
The errors were repeating for the same table insert, no?
The fact that you were able to explicitly script around the problem the wizard had suggests that either MS made a different assumption from your script, somewhere, or that the wizard just outright missed something.
-
To answer some of your questions
1. Yes the old database is up and No I didn't run the migration tool on it...I am not familiar with it.
2. I am not too sure how to interpret your question, in the table I gave as an example it was basically a look up table which had an identity column...and was a very narrow table (not much too it) and only had 59 rows to it. I did not see anything anywhere in the table (column names and data) that referenced "output column". The two rows that had an identity column # of 31 and 47 had different data in the rows so I didn't see any conflict there as far as PK/FK issues...since the only field that was a PK was the identity column.
As for the upper case / lower case statement I just used the generated SQL script from the scripting wizard. Everything had the exact same name with the exception of the SOURCE database had _TEMP at the end of it, so I am not sure that could have caused a problem...
My script was VERY basic...but worked like a charm...
It disabled all triggers for all tables
It created an SET IDENTITY_INSERT ON statement for tables that had identity fields
It built the INSERT...SELECT statement for each table
It set IDENTITY_INSERT OFF for the table after it was done INSERTING rows.
Then enabled triggers
Like I said I am VERY limited in sql 2005, so I very well could have been doing something wrong...however the wizard is very limited in my opinion so I don't think I could have been screwing something up that bad. The options allowed during the import wizard are pretty straight forward vs. the error message I was receiving...
The only other thing I thought about that I couldn't confirm or deny was a system setting in SQL 2005 that I wasn't aware of which would cause a conflict...that was a reach at best but I was pretty desperate at the time.
-
I know this is a SQL Server 2005 related post, but when using the wizard for moving a database from SQL 2012 to SQL 2014 we received the same error. After doing some research, I found this:
We found out that if you have a column in any of your tables named [ErrorCode] or [ErrorColumn] it will throw an error. We re-named the fields and everything ran fine. Apparently, this is a feature of the wizard.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply