SQL Server Performance

DTS-Excel to SQL Import

Discussion in 'SQL Server DTS-Related Questions' started by tfakih, Jul 7, 2006.

  1. tfakih New Member

    I require a SQL DTS package to perform the following:

    - many users create excel files periodically to server
    - Once an excel file has been uploaded, need to read and validate the data
    - then import data into existing database,
    inserting data if record is not present and
    updating data if present
    - errors need to be logged to database and emailed

    Can u please guide me how to do this using DTS in SQL Server 2000

    Thanks in advance.

    -TFakih
  2. dineshasanka Moderator

    What are the validation you need to check on the EXCEL file.
    Possible way of doing this is import excel to temporary table then validate and import to the database you need

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  3. tfakih New Member

    The Combination of the User_ID & Group_ID should be unique.
    My problem is to generate error (if any) & send it to user back,
    How can I do this...can u pls.guide me. I m new to DTS.
    -TFakih
  4. bijesh New Member

    Hi,

    I think you need to put a constraint (User_id and Group_id) on your destination table.
    If there are any duplicate values the constraint will automatically check it and throw error.

    If you have a job that runs DTS, set its property to Quit the job on failure or write it to log file.

    Thanks,

    Bijesh
  5. Adriaan New Member

    Bijesh, such a constraint will cause the import to fail. You need a 'working' table that allows you to have duplicates, and then you process the data, making sure that the duplicates do not enter into the production data.
  6. bijesh New Member

    Thanks Adriaan,

    I agree with you.
    I was under the impression that insertion of duplicate data in DTS needs to be aborted.

    Thanks once again.

    Thanks,

    Bijesh
  7. satya Moderator

    Using such constraints you can set a workflow to fail or pass the package steps in that case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page