none
SSIS check if file was already loaded ?

    Question

  • I want to make a package to load some text files into a database table. Any suggestions on how I can check if a file has been loaded already or if a duplicate file exists, then don't load the duplicate ? 

    Thanks.
    Thursday, September 26, 2013 6:22 PM

Answers

All replies

  • a duplicate file would exist in the database as a number of records. If you check for duplication using a record ID (say number) then you can set the table so it has that ID the primary key or a unique index (if your db supports this) and then an attempt to insert a duplicate would error the package out which you can handle gracefully.

    There are other less elegant solutions, but given how many technical details you shared this is how for I can go.


    Arthur My Blog

    Thursday, September 26, 2013 6:40 PM
    Moderator
  • I mean, if the same filename is found, then do not upload it. If I have a pk for the rows in file, then I will get an error when i insert a duplicate row from a duplicate file. Is there any way to ignore the error and load only the correct part of the data ?

     
    Thursday, September 26, 2013 8:54 PM
  • This is normally done by processing the files from a "drop" directory, and after processing moving them to a "processed" directory.

    If a user redrops the same file again, they get double data imported.

    Thursday, September 26, 2013 9:05 PM
  • Then you simply implement a log table and check against it using a simple SQL query, then the execution flow can be directed using the Precedence Constraints: https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/

    Please note: SSIS doesn't have any built in facilities preventing from processing a duplicate file and shouldn't have.


    Arthur My Blog


    Friday, September 27, 2013 9:15 PM
    Moderator