SQL Server Performance

How do I compare a flat file to a SQL table to find records that are in the table but not the file?

Discussion in 'SQL Server 2005 Integration Services' started by tthomasol, Apr 1, 2008.

  1. tthomasol New Member

    I'm Fairly new to using SSIS and I'm trying to figure out how to approach the following situation.
    I have a flat file with variables that come from another system that I need to harvest and store in a table.
    The flat file looks something like this:
    013|Setting 1
    015|Setting 2
    I want a copy of all active data stored in my table and I want to mark any that have been removed as deleted but not to actually delete them.
    My Table is laid out something like this.
    I wish to read the flat file and 1. Update any changed records 2. Insert Missing Records and 3. Mark for Delete Records that are missing from the flat file. I have figured out how to perform Task 1 and 2 with no problem. My problem is with 3. I need to do some type of outer join on the data that is currently in the table with the data that I'm reading from the flat file. I can't use a lookup (i think) because it is based on the file and shows me what matches and doesn't match from the file's perspective but knows nothing about the rest of the data in the table. Can someone please guide me in the correct direction here?
  2. martins New Member

    In my opinion it would be easiest to insert the data from the flat file into a staging table first. It would then be very easy to left outer join on the staging table.
  3. dineshasanka Moderator

    Without inserting to a table , you can do the merge join in the SSIS. you can have two data streams , one from text fiel and the other from the database. then using a merge join do a left outer join.
    Use conditional split to filter out not in records
  4. tthomasol New Member

    Thanks Dineshasanka!
    I used your guidelines and got it to work exactly as I had hopped and it's all in the SSIS package.
    I used a Merge Join using the Outer Left Join Feature then I used a conditional split to check for Null between the table and the file.
    It works great![:D]
  5. tthomasol New Member

    Thanks for the advice Martins,
    I had a similar problem in another SSIS package I put together a few months back and I solved it as you suggested but in this case I'm trying to do it all within SSIS and not create any additional or temporary tables.
    This is a good way to do it if I can't get my other way to work though.

Share This Page