Alex Rivera | Logout

How can I load a large flat file into a database table using SSIS?

Asked 2011-05-19T15:28:18.737
11

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

Scenario:

Every morning, I get a tab delimited file with 800K records. I need to load it into my database:

  1. Get file from ftp or local
  2. First, I need to delete the one which not exists in new file from database;
    • How can I compare data in tsql
    • Where should I load data from tab delimited file in order to compare it with the file? Should I use a temp table? ItemID is the unique column in the table.
  3. Second, I need to insert only the new records into the database.
  4. Of course, it should be automated.
  5. It should be efficient way without overheating SQL Database

Don't forget that the file contains 800K records.

Sample flat file data:

ID  ItemID  ItemName  ItemType
--  ------  --------  --------
 1  2345    Apple     Fruit
 2  4578    Banana    Fruit

How can I approach this problem?

sql-server-2008 ssis
Edit
Report

1 Answer

22

Yes, SSIS can perform the requirements that you have specified in the question. Following example should give you an idea of how it can be done. Example uses SQL Server as the back-end. Some of the basic test scenarios performed on the package are provided below. Sorry for the lengthy answer.

Step-by-step process:

  1. In the SQL Server database, create two tables namely dbo.ItemInfo and dbo.Staging. Create table queries are available under Scripts section. Structure of these tables are shown in screenshot #1. ItemInfo will hold the actual data and Staging table will hold the staging data to compare and update the actual records. Id column in both these tables is an auto-generated unique identity column. IsProcessed column in the table ItemInfo will be used to identify and delete the records that are no longer valid.

  2. Create an SSIS package and create 5 variables as shown in screenshot #2. I have used .txt extension for the tab delimited files and hence the value *.txt in the variable FileExtension. FilePath variable will be assigned with value during run-time. FolderLocation variable denotes where the files will be located. SQLPostLoad and SQLPreLoad variables denote the stored procedures used during the pre-load and post-load operations. Scripts for these stored procedures are provided under the Scripts section.

  3. Create an OLE DB connection pointing to the SQL Server database. Create a flat file connection as shown in screenshots #3 and #4. Flat File Connection Columns section contains column level information. Screenshot #5 shows the columns data preview.

  4. Co

answered 2011-05-28T04:14:50.307

Your Answer