How can I load a large flat file into a database table using SSIS?
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:
- Get file from ftp or local
- 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.
- Second, I need to insert only the new records into the database.
- Of course, it should be automated.
- 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?
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:
In the SQL Server database, create two tables namely
dbo.ItemInfo
anddbo.Staging
. Create table queries are available under Scripts section. Structure of these tables are shown in screenshot #1.ItemInfo
will hold the actual data andStaging
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.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
andSQLPreLoad
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.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.
Configure the Control Flow Task as shown in screenshot #6. Configure the tasks
Pre Load
,Post Load
andLoop Files
as shown in screenshots #7 - #10. Pre Load will truncate staging table and setIsProcessed
flag to false for all rows in ItemInfo table. Post Load will update the changes and will delete rows in database that are not found in the file. Refer the stored procedures used in those tasks to understand what is being done in theseExecute SQL
tasks.Double-click on the Load Items data flow task and configure it as shown in screenshot #11.
Read File
is a flat file source configured to use the flat file connection.Row Count
is derived column transformation and its configuration is shown in screenshto #12.Check Exist
is a lookup transformation and its configurations are shown in screenshots #13 - #15. Lookup No Match Output is redirected toDestination Split
on the left side. Lookup Match Output is redirected toStaging Split
on the left side.Destination Split
andStaging Split
have the exact same configuration as shown in screenshot #16. The reason for 9 different destinations for both destination and staging table is to improve the performance of the package.All the destination tasks 0 - 8 are configured to insert data into table
dbo.ItemInfo
as shown in screenshot #17. All the staging tasks 0 - 8 are configured to insert data intodbo.Staging
as shown in screenshot #18.On the Flat File connection manager, set the ConnectionString property to use the variable FilePath as shown in screenshot #19. This will enable the package to use the value set in the variable as it loops through each file in a folder.
Test scenarios:
Test results may vary from machine to machine.
In this scenario, file was located locally on the machine.
Files on network might perform slower.
This is provided just to give you an idea.
So, please take these results with grain of salt.
Package was executed on a 64-bit machine with Xeon single core CPU 2.5GHz and 3.00 GB RAM.
Loaded a flat file with
1 million rows
. Package executed in about 2 mins 47 seconds. Refer screenshots #20 and #21.Used the queries provided under Test queries section to modify the data to simulate update, delete and creation of new records during the second run of the package.
Loaded the same file containing the
1 million rows
after the following queries were executed in the database. Package executed in about 1 min 35 seconds. Refer screenshots #22 and #23. Please note the number of rows redirected to destination and staging table in screenshot #22.
Hope that helps.
Test queries: .
--These records will be deleted during next run
--because item ids won't match with file data.
--(111111 row(s) affected)
UPDATE dbo.ItemInfo SET ItemId = 'DEL_' + ItemId WHERE Id % 9 IN (3)
--These records will be modified to their original item type of 'General'
--because that is the data present in the file.
--(222222 row(s) affected)
UPDATE dbo.ItemInfo SET ItemType = 'Testing' + ItemId WHERE Id % 9 IN (2,6)
--These records will be reloaded into the table from the file.
--(111111 row(s) affected)
DELETE FROM dbo.ItemInfo WHERE Id % 9 IN (5,9)
Flat File Connection Columns .
Name InputColumnWidth DataType OutputColumnWidth
---------- ---------------- --------------- -----------------
Id 8 string [DT_STR] 8
ItemId 11 string [DT_STR] 11
ItemName 21 string [DT_STR] 21
ItemType 9 string [DT_STR] 9
Scripts: (to create both tables and stored procedures) .
CREATE TABLE [dbo].[ItemInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ItemId] [varchar](255) NOT NULL,
[ItemName] [varchar](255) NOT NULL,
[ItemType] [varchar](255) NOT NULL,
[IsProcessed] [bit] NULL,
CONSTRAINT [PK_ItemInfo] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [UK_ItemInfo_ItemId] UNIQUE NONCLUSTERED ([ItemId] ASC)) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Staging](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ItemId] [varchar](255) NOT NULL,
[ItemName] [varchar](255) NOT NULL,
[ItemType] [varchar](255) NOT NULL,
CONSTRAINT [PK_Staging] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[PostLoad]
AS
BEGIN
SET NOCOUNT ON;
UPDATE ITM
SET ITM.ItemName = STG.ItemName
, ITM.ItemType = STG.ItemType
, ITM.IsProcessed = 1
FROM dbo.ItemInfo ITM
INNER JOIN dbo.Staging STG
ON ITM.ItemId = STG.ItemId;
DELETE FROM dbo.ItemInfo
WHERE IsProcessed = 0;
END
GO
CREATE PROCEDURE [dbo].[PreLoad]
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.Staging;
UPDATE dbo.ItemInfo
SET IsProcessed = 0;
END
GO
Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10:
Screenshot #11:
Screenshot #12:
Screenshot #13:
Screenshot #14:
Screenshot #15:
Screenshot #16:
Screenshot #17:
Screenshot #18:
Screenshot #19:
Screenshot #20:
Screenshot #21:
Screenshot #22:
Screenshot #23:
Assuming you are using SQL Agent (or similar scheduler)
Reqs 1/4) I would have a precursor step handle the FTP and/or file copy steps. I don't like to clutter my packages with file manipulation if I can avoid it.
Reqs 2/3) At the control flow level, the package design is going to look like an Execute SQL task connected to a Data Flow connected to another Execute SQL task. As @AllenG indicated, you'd be best served by loading into a staging table via the Data flow task. The first Execute SQL Task will purge any rows from the staging table (TRUNCATE TABLE dbo.DAILY_STAGE)
Approximate table design looks like this. The MICHAEL_BORN table is your existing table and the DAILY_STAGE is where your data flow will land.
CREATE TABLE DBO.MICHAEL_BORN
(
ID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED
, ItemID int NOT NULL
, ItemName varchar(20) NOT NULL
, ItemType varchar(20) NOT NULL
)
CREATE TABLE dbo.DAILY_STAGE
(
ItemID int NOT NULL PRIMARY KEY CLUSTERED
, ItemName varchar(20) NOT NULL
, ItemType varchar(20) NOT NULL
)
For demonstration purposes, I will load the above tables with sample data via TSQL
-- Original data
INSERT INTO
dbo.MICHAEL_BORN
VALUES
(2345,'Apple','Fruit')
, (4578, 'Bannana','Fruit')
-- Daily load runs
-- Adds a new fruit (pear), corrects misspelling of banana, eliminates apple
INSERT INTO
dbo.DAILY_STAGE
VALUES
(7721,'Pear','Fruit')
, (4578, 'Banana','Fruit')
The Execute SQL task will take advantage of the MERGE statement available in 2008+ editions of SQL Server. Please note the trailing semi-colon is part of the MERGE statement. Failure to include it will result in an error of "A MERGE statement must be terminated by a semi-colon (;)."
-- MERGE statement
-- http://technet.microsoft.com/en-us/library/bb510625.aspx
-- Given the above scenario, this script will
-- 1) Update the matched (4578 bannana/banana) row
-- 2) Add the new (pear) row
-- 3) Remove the unmatched (apple) row
MERGE
dbo.[MICHAEL_BORN] AS T
USING
(
SELECT
ItemID
, ItemName
, ItemType
FROM
dbo.DAILY_STAGE
) AS S
ON T.ItemID = S.ItemID
WHEN
MATCHED THEN
UPDATE
SET
T.ItemName = S.ItemName
, T.ItemType = S.ItemType
WHEN
NOT MATCHED THEN
INSERT
(
ItemID
, ItemName
, ItemType
)
VALUES
(
ItemID
, ItemName
, ItemType
)
WHEN
NOT MATCHED BY SOURCE THEN
DELETE
;
Req 5) Efficiency is totally based on your data and how wide your rows are but it shouldn't be terrible.
-- Performance testing
-- Assumes you have a similar fast row number generator function
-- http://billfellows.blogspot.com/2009/11/fast-number-generator.html
TRUNCATE TABLE dbo.MICHAEL_BORN
TRUNCATE TABLE dbo.DAILY_STAGE
-- load initial rows
-- 20ish seconds
INSERT INTO
dbo.MICHAEL_BORN
SELECT
N.number AS ItemID
, 'Spam & eggs ' + CAST(N.number AS varchar(10)) AS ItemName
, 'SPAM' AS ItemType
--, CASE N.number % 2 WHEN 0 THEN N.number + 1000000 ELSE N.number END AS UpTheEvens
FROM
dbo.GenerateNumbers(1000000) N
-- Load staging table
-- Odds get item type switched out
-- Evens get delete and new ones created
-- 20ish seconds
INSERT INTO
dbo.DAILY_STAGE
SELECT
CASE N.number % 2 WHEN 0 THEN N.number + 1000000 ELSE N.number END AS ItemID
, 'Spam & eggs ' + CAST(N.number AS varchar(10)) AS ItemName
, CASE N.number % 2 WHEN 0 THEN 'SPAM' ELSE 'Not much spam' END AS ItemType
FROM
dbo.GenerateNumbers(1000000) N
-- Run MERGE statement, 32 seconds 1.5M rows upserted
-- Probably fast enough for you
I just want to give my idea for the next guy who may pass by this question. So I'm going to suggest my idea for each scenario's.
1. Getfile from FTP or local.
I would suggest you to use Drop box, Google Drive or any other file syncing cloud services of your choice see this link for detail.
2. I would suggest loading all flat file data to staging table as you suggested Then comparing the data would be easily done by using MERGE between your staging table and Target table on your unique column (ID). You can see this link for how to use merge script. The 2nd & 3rd scenarios will be solved if you are using MERGE Script.
For the last two scenarios i suggest you use SQL JOB to automatically run the package and schedule it at off hours or on time where the server is not busy.Please take a look at the link for detail on how to Run a Package Using a SQL Server Agent Job just type it on your favorite search engine and you will find tons of blogs that shows how its done.