title: How can I load a large flat file into a database table using SSIS?
body: <p>I'm not sure how it works so I'm looking for the right solution. I think SSIS is the right way to go but I have never used it before</p>
  <h3>Scenario:</h3>
  <p>Every morning, I get a tab delimited file with 800K records. I need to load it into my database:</p>
  <ol>
  <li>Get file from ftp or local</li>
  <li>First, I need to delete the one which not exists in new file from database;
  <ul>
  <li>How can I compare data in tsql</li>
  <li>Where should I load data from tab delimited file in order to compare it with the file? Should I use a temp table? <code>ItemID</code> is the unique column in the table.</li>
  </ul>
  </li>
  <li>Second, I need to insert only the new records into the database.</li>
  <li>Of course, it should be automated.</li>
  <li>It should be efficient way without overheating SQL Database</li>
  </ol>
  <p>Don't forget that the file contains 800K records.</p>
  <h3>Sample flat file data:</h3>
  <pre><code>ID  ItemID  ItemName  ItemType
  --  ------  --------  --------
   1  2345    Apple     Fruit
   2  4578    Banana    Fruit
  </code></pre>
  <p>How can I approach this problem?</p>