Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Angry Unanswered: DTS job to Oracle Table.

    I'm having a problem with DTS.
    I've got a table on a Microsoft SQL 2000 server that I'm attempting to export to an Oracle Table.

    The Oracle Table has a Primary key set, that automatically generates it's own keys.
    However, using the DTS export job I continually get:

    ------------------
    Error Source: Microsoft Data Transformation Services (DTS) Data Pump
    Error Description:Insert error, column 1 ('INPT_PKT_HDR_ID', DBTYPE_NUMERIC), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints.
    Error Help File:sqldts80.hlp
    Error Help Context ID:30702
    ------------------

    Now, I'm not attempting to insert anything into this primary key field, so why oh why am I getting this error message?

    I'm using a DTS ActiveX Script to do the transformation as follows:
    ------------------
    '************************************************* *****
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************************************************* *****
    Function Main()
    DTSDestination("PKT_CTRL_NBR") = DTSSource("PKT_CTRL_NBR")
    DTSDestination("CUST_RTE") = DTSSource("CUST_RTE")
    Main = DTSTransformStat_OK
    End Function
    ------------------

    So nothing too scarry or difficult there.

    I'm using an ODBC Oracle connection to the get to the Oracle table, although I've also tried using the Microsoft OLE DB Provider for Oracle.
    Both give me the same error.

    Importing data from Oracle to Oracle works.

    Can someone please suggest some ideas to fix this problem?

    Thanks.

  2. #2
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34

    Lightbulb

    It sounds as though you are trying to insert a null value into a column which does not accept them. Can you confirm that there are no NULL or non conforming values in you source table col1?

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by SQLSurfer
    It sounds as though you are trying to insert a null value into a column which does not accept them. Can you confirm that there are no NULL or non conforming values in you source table col1?
    Nope. The initial colum of the source table contains data. This column is being inserted into another column on the destination database.
    At no point am I inserting NULL values into any of the columns on the destination as the source columns all contain data.

    I believe all of the source columns match upto the destination columns in terms of data types as well (although date fields I'm a bit unsure of how they are mapped across).

    Any ideas at all to check would be more than welcome as it's probably something blatantly simple that I've overlooked.

  4. #4
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34
    The error appears to point the finger at column 1 'INPT_PKT_HDR_ID'. What is the SQL datatype of this column and its properties compared to the other DB table?

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Originally posted by SQLSurfer
    The error appears to point the finger at column 1 'INPT_PKT_HDR_ID'. What is the SQL datatype of this column and its properties compared to the other DB table?
    'INPT_PKT_HDR_ID' only exists in the destination table (ORACLE).

    It's set-up as follows:

    COLUMNS
    ======
    Column | PK | Data Type | NULL? | Default
    -------------------------------------------------
    INPT_PKT_HDR_ID | 1 | NUMBER(9) | N |

    TRIGGER
    ======
    CREATE OR REPLACE TRIGGER "TESTDB".TIB_INPT_PKT_HDR
    BEFORE INSERT ON INPT_PKT_HDR FOR EACH ROW
    DECLARE

    ID NUMBER(9);

    BEGIN

    ID := 0;
    SELECT INPT_PKT_HDR_SEQ.NEXTVAL INTO ID FROM DUAL;

    :NEW.INPT_PKT_HDR_ID := ID;

    END;

Posting Permissions

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