Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006

    Question Unanswered: DTS Transform issue

    I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.

    Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?

    If there is could you be specific in how to setup the DTS transformation script.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    moving thread to SQL Server forum (the SQL forum is for the SQL language itself) | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Import everything to a raw table first.

    Then you can make all the validation you want before inserting the data into your system.

  4. #4
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'd probably just suck the data from the external source into a working table that had pure Unicode (NVARCHAR) character columns. Once it was there, you can "sanitize" it any way you need to using Transact-SQL.

    Another option that saves on disk and keeps the package conceptually "atomic" would be to handle the exceptions within the DTS package itself. Instead of using a default "flow" transformation within the DTS column mapping, you could use script to do whatever validation suited your needs.


  5. #5
    Join Date
    Jun 2006
    HHmmm, well it seems like you both are saying the same thing.
    "raw table" and "working table" are they the same thing?
    All fileds are nvarchar correct?
    and use a copy column to column transform correct?


Posting Permissions

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