Table transfer errors

DaveDB

Hello,

I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.

- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of
"output column "ErrorCode" (79)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)

This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...

Any help would be appreciated...

Thanks,

David

Jamie Thomson

This is a strange on. I know exactly what the error means but I don't understand how or why it would be produced by the Import Wizard. You'll have to open up the package and investigate.

Can you share the package I'd be interested to see what is wrong here.

-Jamie

DaveDB

Hello Jamie,

Unfortunately I cannot share my companies data, however I did discover that none of the primary keys were carried over to the tables that were successful. I am transfering over 300 tables and all are successful accept 1 table. As is said the primary keys are not being carried over, so maybe this is a clue as to why this is happening...

Jamie Thomson

That wouldn't explain the problem. I don't understand why there are columns called ErrorCode in the data-flow.

You don't need to share any data. Just the package.

-Jamie

DaveDB

I noticed another issue...

The tables that did succeed in the transfer are without constraints. Is there a switch I have to flip in order to have the Import Export wizard transfer over the constraints

Thanks,

David

Ryan.Kelley

I have run into this when I have two columns that have the same column names. "ErrorCode" maybe a column name in one of your tables

ErrorCode" (79)" and "output column "ErrorCode" (14)". 79 and 14 are table ids that SSIS has assigned in the wizard. Find these tables.

I have seen this error specific to a data conversion task that is doing and explict conversion of the data types within the wizard.

 

Fix:..Identify the tables with the columns above. go into the package and in the data conversion componant. give the output destination a  name of "ErrorCode1" and the metadata will propagate to your destination.

 

not a big deal, unless all of your tables have that column.... but from there you can just update the XML manually.

 

Ryan

Jamie Thomson

Ryan.Kelley wrote:

I have run into this when I have two columns that have the same column names. "ErrorCode" maybe a column name in one of your tables ErrorCode" (79)" and "output column "ErrorCode" (14)". 79 and 14 are table ids that SSIS has assigned in the wizard. Find these tables. I have seen this error specific to a data conversion task that is doing and explict conversion of the data types within the wizard. Fix:..Identify the tables with the columns above. go into the package and in the data conversion componant. give the output destination a name of "ErrorCode1" and the metadata will propagate to your destination. not a big deal, unless all of your tables have that column.... but from there you can just update the XML manually. Ryan

But currently it will still attempt to create a new table with two columns called ErrorCode. Rename a pipeline column will not change the name of teh column in the destination. THAT is what you will have to change.

I still don't understand why you have columns called ErrorCode in the pipeline. I know under what circumstances ErrorCode columns would get created but I don't understand why those circumstances would arise when using the Import/Export wizard. Admittedly I don't use the wizard much.

-Jamie

DaveDB

Hi Jamie,

I do indeed have a column named errocode, (should've checked that). So let me get this straight, in order for the data transfer to be successful the data that is being transfered cannot include the same column name as a column that is already in the data being transfered That's pretty strict when it comes to database arcitecture, but I am sure that most companies will have the same column name in a different table.

Thanks again for your responses...

David

PoorSQLGuy

I was also getting the same error and upon investigation, I found that there is no need of changing anything in package. (No need to change source column name or destination column name or even drop "ErrorCode" column from pipeline.

just ignore the error, SSIS is throwing for the table in question. It is transferring data properly, just throws that error.

Jamie Thomson

DaveDB wrote:

Hi Jamie, I do indeed have a column named errocode, (should've checked that). So let me get this straight, in order for the data transfer to be successful the data that is being transfered cannot include the same column name as a column that is already in the data being transfered That's pretty strict when it comes to database arcitecture, but I am sure that most companies will have the same column name in a different table.

Dave,

No, that is not the case. There is no restriction on column names. The only reason that ErrorCode is intriguing is because in certain circumstances the pipeline can introduce a column called ErrorCode (i.e. to handle errors). What I am surprised about is why the import wizard would introduce a column called ErrorCode. That is why I asked if you could share the package.

-Jamie

DaveDB

Hello,

I noticed that the data did indeed get inserted into the table, however I cannot ignore this error, because after that error occurs, it stops importing the remaining tables. Is there someway I can ignore this error and if so, my other question then would be, how can I retain the original constraints.

Thanks for your time,

David

PoorSQLGuy

Jamie,

This error can be reproduce very easily. Just create a table with multiple columns and make sure that one of them is named as "ErrorCode". Create the table in Source and Destination database.

While designing pipe line make sure Data access Mode is "Table Or view" at source and "Table or View - fast load" in destination. Add few rows at the source and execute the package. You will see the error.

Phil Brammer

PoorSQLGuy wrote:

Jamie, This error can be reproduce very easily. Just create a table with multiple columns and make sure that one of them is named as "ErrorCode". Create the table in Source and Destination database. While designing pipe line make sure Data access Mode is "Table Or view" at source and "Table or View - fast load" in destination. Add few rows at the source and execute the package. You will see the error.

The deal is that in an OLE DB Source, the OLE DB Source Error Output uses the column names from the External Columns that are chosen in the mappings page. By that I mean, if when I select a table that has ErrorCode as a column name and alias it to MyErrorCode, the OLE DB Source Error Output still uses "ErrorCode" as the column name, not "MyErrorCode."

This sounds like a bug to me.  I understand that I cannot have duplicate names, but the OLE DB Source Error Output should be using the column names from the Output Columns section of the advanced editor, not choosing the names from the External Columns.  I should be able to alias the "ErrorCode" column in the dataflow so that I can work with that column.

Phil Brammer

I've submitted a bug for this. Please vote to confirm. Also be sure to add meaningful comments.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx FeedbackID=252471

Jamie Thomson

Phil Brammer wrote:

PoorSQLGuy wrote: Jamie, This error can be reproduce very easily. Just create a table with multiple columns and make sure that one of them is named as "ErrorCode". Create the table in Source and Destination database. While designing pipe line make sure Data access Mode is "Table Or view" at source and "Table or View - fast load" in destination. Add few rows at the source and execute the package. You will see the error. The deal is that in an OLE DB Source, the OLE DB Source Error Output uses the column names from the External Columns that are chosen in the mappings page. By that I mean, if when I select a table that has ErrorCode as a column name and alias it to MyErrorCode, the OLE DB Source Error Output still uses "ErrorCode" as the column name, not "MyErrorCode." This sounds like a bug to me. I understand that I cannot have duplicate names, but the OLE DB Source Error Output should be using the column names from the Output Columns section of the advanced editor, not choosing the names from the External Columns. I should be able to alias the "ErrorCode" column in the dataflow so that I can work with that column.

Yeah I agree with all of that. it does sound like a bug. Anyone want to submit it with a repro

-Jamie