Network technologies by Kaiming Liao: SQL:FOREACH LOOP

Import data from many flat files into a table

Creating a destination table

USE [AdventureWorks]
GO
CREATE TABLE [dbo].[Currency](
[AverageRate] [numeric](18, 8) NULL,
[InceptionDate] [datetime] NULL,
[CurrentRate] [numeric](18, 8) NULL
)

Creating flat files and Save them in C:\AOL folder

Sample1.txt:

1.00010001;9/3/2001 0:00;0.99960016

1.00020001;9/4/2001 0:00;1.001001001

1.00020004;9/5/2001 0:00;0.99990001

1.00020002;9/6/2001 0:00;1.00040015

Sample2.txt

1.00050025;9/7/2001 0:00;0.99990001

1.00050026;9/8/2001 0:00;1.001001001

1.00050045;9/9/2001 0:00;1

1.00010001;9/10/2001 0:00;1.00040016

1.00020004;9/11/2001 0:00;0.99990001

1.00020004;9/12/2001 0:00;1.00110121
Creating one file importing package:

BIDS--new integration project--rename the package.dtsx to Lesson 1.dtsx

Connection to AOL Folder manager will access the c:\sample1.txt, sample2.txt, etc.

General--FileName: C:\AOL\sample1.txt

Advanced -- the column names and data type

Queen.AdventureWorks connection manager will access the AdventureWorks database on SQL Server:QUEEN.

Drag the Data Flow item onto the Control Flow pane and double click it to navigate onto the Data Flow pane

Double-click on Flat File Source and make sure that the column names are correctly mapped. And then, double-click on the OLE DB Destination, click on the Mapping, you should have the following:

Execute the package;

In Query Window, SELECT * FROM dbo.Currency. All data in sample1.txt should be imported into dbo.Currency table of AdventureWorks database.

===

Import data from many flat files --FOREACH LOOP

Copy the Lesson 1.dtsx package and paste it to create the Lesson 2.dtsx package;

Double-click the Lesson 2.dtsx package;

You are in the Control Flow Pane. Drag the FOREACH Loop Container onto the Control Flow Pane; double-click it to open the FOREACH LOOP Editor. Folder: C:\AOL and Files: Sample??.txt.