Post reply

Identity property problem

  • July 17, 2008 at 3:34 am

    #122564

    Hi Team,

    I am finding a small problem in my package.

    I am having table named "TableA" with column id - identity, name - varchar.

    Now i need to fetch the data from source and populate into this table.

    The data resembles,

    ------------------

    id name

    -------------------

    1 james

    15 vinay

    5 john

    -------------------

    I got the data flow task, specified source and destination, mapped the source and destination columns,

    am getting an error indicating "id" is read only.

    I have written execute sql task to change the identity property of the table. But, I am unable to run the package. its showing build error.

    Error 1 Validation error. rl_Publication: OLE DB Destination [4695]: Failure inserting into the read-only column "id". group.dtsx 0 0

    Error 2 Validation error. rl_Publication: OLE DB Destination [4695]: Column metadata validation failed. group.dtsx 0 0

    Error,

    TITLE: Package Validation Error

    ------------------------------

    Package Validation Error

    ------------------------------

    ADDITIONAL INFORMATION:

    Error at TableA[OLE DB Destination [4695]]: Failure inserting into the read-only column "id".

    Error at TableA [OLE DB Destination [4695]]: Column metadata validation failed.

    Error at TableA[DTS.Pipeline]: "component "OLE DB Destination" (4695)" failed validation and returned validation status "VS_ISBROKEN".

    Error at TableA [DTS.Pipeline]: One or more component failed validation.

    Error at TableA: There were errors during task validation.

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------

    BUTTONS:

    OK

    am unable to run the package. Please help me.

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • July 17, 2008 at 3:55 am

    #844232

    Hi Venkatesan,

    Just check the Keep Identity check box of Destination and try again.

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • July 17, 2008 at 4:07 am

    #844238

    Thanks Narayanan,

    Am having another condition like id1 column in the same table which doesnt have identity column.. but i need to insert values for ex.

    source db

    id name

    -----------

    1 vijay

    20 arun

    3 john

    ---------------

    Destination db

    id name id1

    -----------

    1 vijay 1

    20 arun 2

    3 john 3

    --------------

    So need to put an identity on this column... so both are clashing..

    else shall i write a counter to execute the id1 value.

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • July 17, 2008 at 4:20 am

    #844243

    No need to write a counter.

    Suppose Your tables look like this:

    CREATE TABLE TableA (ID int, Name VARCHAR(30)) --Source

    INSERT INTO TableA SELECT 1,'james' union Select 15,'vinay' union select 5,'john'

    CREATE TABLE TableB (ID1 int IDENTITY, Name VARCHAR(30), ID2 int) -- Destination

    Now againg uncheck the Keep Identity check box.

    And in Column Mappings, Make input Column ignore for Destination column ID1 and

    map Source ID to Destination ID2 and Source Name to Destination Name

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • July 17, 2008 at 7:17 pm

    #844737

    Thanks Hari..

    I got it ..

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • July 18, 2008 at 8:59 am

    #845119

    Hari,

    I had a similar issue as well, and your answer of checking the "keep Identity" worked, thanks. Do you know if by doing this the next record entered into the table will have the max identity + 1?

    thanks,

    rusty

  • July 20, 2008 at 11:01 pm

    #845481

    Hi Rusty,

    You are right.

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • April 11, 2011 at 8:19 am

    #1309840

    Hi hari,

    What if tables have relationships with each other. Will it take care of foreignID insert.

    4x4 photos

  • April 11, 2011 at 8:35 am

    #1309850

    Please post new questions in a new thread. This thread is 3 years old and the person you are addressing hasn't logged in in 3 months.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply