Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Posts
    45

    Unanswered: Problem DTSing into table with Identity field

    Hello everyone,

    I have a table X that has two columns (names have been changed to protect the guilty and innocent alike):
    ID which is an identity column, and
    Data which is a varchar.

    I am trying to DTS data from a text file into this table. I want the identity column ID to auto-populate and each row of information from the file to be stored in the Data field.

    I am creating the DTS package using the wizard in Enterprise Mgr. No matter what I try, I keep getting the following error:

    Cannot insert the value NULL into column 'ID', table 'X'; column does not allow nulls. INSERT fails.

    Any advice on what I need to do to get this working? It seems like such a simple thing and I'm getting very frustrated.

    Thanks in advance.
    Cathy

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    DTS sucks.

    Are you using the wizard. Have you tried hitting the transform button and ignore on the source column.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Does every record in the text file have an ID value? Sounds like there must be NULLs.

    ddave

  4. #4
    Join Date
    Jun 2005
    Posts
    45
    Hello again,

    Thrasymachus,
    Yes, I'm using the wizard and setting the transformation to <ignore> the identity column. And yes, DTS does suck! :-)

    dolfandave,
    My file does not have values for the identity column, I'm trying to get them to be auto-generated by SQL Server, like they would if I did an insert into the table in isql.

    Thanks,
    Cathy

  5. #5
    Join Date
    Aug 2001
    Posts
    24

    Identity Insert

    did u try checking the "Enable Identity insert" checkbox in the Tranform Data Task properties?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •