curiousss


I am a newbie, who created an ordinary data flow task, connecting Ole db source to ole db destination, getting the following error.
Validation error. Data Flow Task Shop: OLE DB Destination Shop [16]: Failure inserting into the read-only column "id".

Moreover, in another flow where I've the columns name and family name in the source db, and only name in the destination db. Planned to concenate these in the derived transformation editor, creating an expression: name+ '' '' +familyname. Derived column name: Name
but it does not accept it...
Greateful for some help!





Re: Failure read-only column "id"

Rafael Salas


 curiousss wrote:

I am a newbie,  who created an ordinary data flow task, connecting Ole db source to ole db  destination, getting the following error.
Validation error. Data Flow Task Shop: OLE DB Destination Shop [16]: Failure inserting into the read-only column "id".

You may be trying to insert an explicit value into a column that has been defined as Identity and the IDENTITY_INSERT  option is set to OFF. If you really want to assigned a value you can SET IDENTITY_INSERT  ON; otherwise, just go to the destination component in the dataflow and remove the mapping to that column.

http://msdn2.microsoft.com/en-us/library/ms188059.aspx

 curiousss wrote:

Moreover, in another flow where I've the columns name and family name in the source db, and only name in the destination db. Planned to concenate these in the derived transformation editor, creating an expression: name+ '' '' +familyname. Derived column name: Name
but it does not accept it...
Greateful for some help!

The syntax looks right. Is there any error

This is the topic in BOL that talks about it:

http://msdn2.microsoft.com/en-us/library/ms138036.aspx

 

 







Re: Failure read-only column "id"

David Frommer

Rafael Salas wrote:

You may be trying to insert an explicit value into a column that has been defined as Identity and the IDENTITY_INSERT option is set to ON. If you really want to assigned a value you can SET IDENTITY_INSERT OFF; otherwise, just go to the destination component in the dataflow and remove the mapping to that column.

It is early and I am very tired, but, don't you have ON and OFF reversed in the above :-)







Re: Failure read-only column "id"

Rafael Salas

You are totally right.

Sorry for the mess. That is what you get when replying posts on New Year's Day after the big party !

 I have corrected my original post.






Re: Failure read-only column "id"

curiousss

Thank you very much!
How do I find the T-sql editor
In the Data menu "
On the Data menu, point to T-SQL Editor, and click New Query Connection. "
But in my Data-menu the selections are gray... not selectable...







Re: Failure read-only column "id"

Rafael Salas

Not sure what ay be your problem. I have tha option available just after opening BIDS; then I am prompted to chose a DB connection




Re: Failure read-only column "id"

sushnair

Hey Hi,

Yes, you are absolutely write. We don't need to map the identity columns they will be automatically inserted when records get inserted into the Table through are SSIS package.

Hey guys also if anyone gets "Validation Error" , then just try using Data Conversion and map this to the OLE DB Destination.