Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004

    Unanswered: Import some data from Access to SQL Server

    I have created some DB tables on SQL Server. Now I want to import some data from Access using Data Transformation Services.

    The APPLICATION table I have created has the following columns:

    AppID, AppName, AppMnem, GripsID, Decommissioned, Last Modified, Modified By

    AppID is the primary key and is an identity column with an autoincrement on insert.

    The query I used to import the data from Access is:

    select `T>Application>General`.`Application Name`, `T>Application>General`.`Application Mnem`, `T>Application>General`.`GripsID`, `T>Application>General`.`Decommissioned`, `T>Application>General`.`Last Modified`, `T>Application>General`.`Modified By`
    from `T>Application>General`
    where `T>Application>General`.`Application Mnem`='TMS'
    order by `T>Application>General`.`Application Mnem`

    The transformation I used is:

    '************************************************* *********************
    ' Visual Basic Transformation Script
    ' Copy each source column to the
    ' destination column
    '************************************************* ***********************

    Function Main()
    DTSDestination("AppName") = DTSSource("Application Name")
    DTSDestination("AppMnem") = DTSSource("Application Mnem")
    DTSDestination("GripsID") = DTSSource("GripsID")
    DTSDestination("Decommissioned") = DTSSource("Decommissioned")
    DTSDestination("Last Modified") = DTSSource("Last Modified")
    DTSDestination("Modified By") = DTSSource("Modified By")
    Main = DTSTransformStat_OK
    End Function

    However, I keep gettting the following error:

    Error at destination for row number 1. Errors encountered so far in this task: 1.
    Instert error, column 1 ('AppID', DBTYPE_I4), status 10: Integrity violation; attempt to insert null data or data which violates constraints.
    Unspecified Error

    Any ideas as to what the problem is?

    Thank in advance

  2. #2
    Join Date
    Nov 2003
    San Francisco, CA USA

    Check your constraints

    Check all your constraints on your target table. It's clear that you're trying to upload data that violates them. If you don't see anything, try this (an MSSQL guru could give you better advice perhaps but this is worth trying).

    Remove the autoincrement field for your ID. It doesn't allow nulls. It may be that it doesn't autoincrement with a batch upload from DTS. Again, a guru would be able to tell you -- my expertise is not in MS SQL Server. But you notice, the error points to this very field so it is the suspect one.

    Upload your data. Then add the autoincrement field *after* the upload. It should increment just fine.

    See if that doesn't work.


  3. #3
    Join Date
    Aug 2004
    Do you know is there any way I can remove this column without losing all the relationships it has with other tables?



  4. #4
    Join Date
    Nov 2003
    San Francisco, CA USA
    There is no way. So do this instead. Edit your autonumber field to make it a number field - not an autoincrement - and change it to permit null values.

    That way you keep the field and the relationships you already established.

    Import your data and then change the field back to autoincrement. I think that'll work.

Posting Permissions

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