dcsimg
Results 1 to 8 of 8

Thread: DTS Problem

  1. #1
    Join Date
    Apr 2005
    Posts
    4

    DTS Problem

    Hi All,

    I have a DTS package that is transfering data from SQL Server 2000 (SP3) to Sybase SQL Anywhere 5.0 database. The package fails saying that a field cannot be null. I preview the data from the query and the data is in the field and I check test the transformation and the data is there, the SQL Server data type is INT and the sybase data type is varchar(30). I'm stumped, any help would be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    Did you query the table to see if there is any null value, test transformation in dts designer does not retrieve all rows. If you execute the step then it will run for all rows.

    Also convert int to varchar(30) when you query from the sql table.

  3. #3
    Join Date
    Apr 2005
    Posts
    4
    I have tried actually changing the data type on the field in SQL Server to varchar(30) to no effect. Since this is in a development environment I only have one record in the table so the NULL value is not an issue. Any other suggestions??????

    thanks

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    Check whether the columns are mapped correctly. What kind of transformation are you using?

  5. #5
    Join Date
    Apr 2005
    Posts
    4
    I am using column copy, and I have a one to one transformation. I test each transformation and the data is written to a text file for each column. Something is getting screwed up on the Sybase end. Should I do all of the columns in a single transformation??????

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    What driver are you using for Sybase?. Sometime using different driver helps if you have a choice.

  7. #7
    Join Date
    Feb 2003
    Posts
    1,048
    Do you have any fields where you are explicitly trying to insert Null during the transformation? Defaults and auto-increment (identity) fields get auto-populated if you fail to insert anything into the column. However, if you try to explicityly insert Null, auto-increments and defualts are triggered, and it tries to insert Null causing the error that you see.

    At least, SQL Server works that way. Sybase might also.

  8. #8
    Join Date
    Apr 2005
    Posts
    4
    Ah, a moment of inspiration. The column that was giving me the error message wasn't actually where the problem was. There is an activity code column that I was using a code that was not listed in the activity table so it that constraint was actually violated. It does seem weird that that wasn't the error message I received.

    Thanks for the help

Posting Permissions

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