Import Multiple Files to SQL Server Using DTS

Wednesday Mar 24th 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK discusses two different methods of importing flat files to SQL Server tables using a batch file and DTS package.

In the typical IT environment, it is often necessary to import flat files to SQL Server tables. Sometimes it is necessary to import many files at the same time from same or different folders. In my previous article, I mentioned how to import such files using BULK Insert and BCP utility. In this article, I am going to discuss how to import all the files from a particular folder to SQL Server using a batch file and DTS package.

Simulation

Let's simulate the whole scenario for importing multiple files. First, let's create a folder C:\MyImport and create 3 files, a.csv, b.csv and c.csv with the below content. Also, create a table in SQL Server to hold the imported data.

Files

C:\MyImport\a.csv

1, MAK, A9411792711, 3400.25
2, Claire, A9411452711, 24000.33
3, Sam, A5611792711, 1200.34
4, Wright, A5611792711, 1200.34
5, Richard, G561d792755, 1223.34
6, Valarie, B5611792788, 1240.32

C:\MyImport\b.csv

11, Rubon, 9671792711, 400.14
22, Mike, 9418952711, 4000.56
39, Hsu, 75611792511, 1230.00

C:\MyImport\c.csv

69, Lucy, 8411992710, 305.11
45, Grace, 3413452713, 246.52
33, Saint, 5461795716, 1278.70

Table


Create Database Bank
Go
Use Bank
go
Create table Account([ID] int, Name Varchar(100), 
AccountNo varchar(100), Balance money)
Go
Create table logtable (id int identity(1,1), 
	Status varchar(500), 
	Importeddate datetime default getdate())
Go
use master
go
sp_addlogin 'importuser','import','Bank'
go
use Bank
go
sp_adduser 'importuser'
go
sp_addrolemember 'db_datareader','importuser'
go
sp_addrolemember 'db_datawriter','importuser'
go

Create DTS Package

Step1: Create Global variables FileName, ServerName and DatabaseName in the DTS package as shown below.

Step 2: Create connections and transformation.

Create the Text connection and SQL Server connection and add the transformation displayed below.

Step 3: Complete the mapping as shown below.

Step 4: Add dynamic tasks and link the "SQLServer" connection to the global variable "ServerName", link "inputfile" to the global variable "FileName" and link "initial catalog" in "SqlServer" connection to "DatabaseName".

Step 5: Add Success workflow between Dynamic Properties and InputFile Connection .

Step 6: Add a SQL Task to write to the logtable as shown below

SQL

Insert into LogTable (Status) values (?)

Assign parameter value

Click for larger image

Click on parameter and select the Global Variable "FileName" assigned to Parameter 1

Step 6: Add Success workflow between the SQL Server connection and SQL task as shown below.

Click for larger image

Step 7: Save the package to a structured storage file. You can also save it in SQL Server. In this article, I am discussing only structured storage files.

Click for larger image

Create batch file c:\MyImport\Import.bat as shown below


REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Import all csv files to SQL Server using DTS
 
REM Export DIR listing to C:\MyImport\Dirlist.txt
dir c:\MyImport\*.csv /b > C:\MyImport\Dirlist.txt
 
REM Execute DTS package for every file name in the Dirlist.txt
 
for /f "tokens=1,2,3" %%i in (C:\MyImport\Dirlist.txt) 
	do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" -F
	"c:\myimport\myimportdts.dts" -Uimportuser -Pimport  -A"ServerName"=
	"SQL" -A"FileName"="c:\myimport\%%i" -A"DatabaseName"="Bank"
 
REM Rename all the files with "old" as suffix
ren c:\myimport\*.csv *.csvold

Execute the batch file

When the batch file is executed, it creates a file, DirList.txt, under c:\myimport that stores all of the filenames that are under c:\myimport based upon the wildcard used and passes the filenames to DTSrun.exe with the necessary parameters. In this case, the DirList.txt has the following values:

C:\MyImport\DirList.txt

a.csv
b.csv
c.csv

Note: Replace the values of servername, filename and databasename in the batch file according to your environment.

Result

The batch file imports all the files into the SQL Server table as shown below.

1

 MAK

 A9411792711

3400.25

2

 Claire

 A9411452711

24000.33

3

 Sam

 A5611792711

1200.34

11

 Rubon

9671792711

400.14

22

 Mike

9418952711

4000.56

39

 Hsu

75611792511

1230

69

 Lucy

8411992710

305.11

45

 Grace

3413452713

246.52

33

 Saint

5461795716

1278.7

It also stores the log information in the logtable as shown below.

1

c:\myimport\a.csv

3/1/04 10:53 AM

2

c:\myimport\b.csv

3/1/04 10:53 AM

3

c:\myimport\c.csv

3/1/04 10:53 AM

This batch file also renames all of the .csv files to .csvold as shown below:

If you are calling a DTS package stored on a Server, then use the batch file below.

Batch file to run DTS package stored in the server


REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Import all csv files to SQL Server using DTS
 
REM Export DIR listing to C:\MyImport\Dirlist.txt
dir c:\MyImport\*.csv /b > C:\MyImport\Dirlist.txt
 
REM Execute DTS package for every file name in the Dirlist.txt
 
for /f "tokens=1,2,3" %%i in (C:\MyImport\Dirlist.txt) 
	do "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\DTSrun.exe" -S
	"SQL" -N"myimportdts" -Uimportuser -Pimport  -A
	"ServerName"="SQL" -A"FileName"="c:\myimport\%%i" -A"DatabaseName"="Bank"
 
REM Rename all the files with "old" as suffix
ren c:\myimport\*.csv *.csvold

Conclusion:

As mentioned earlier, the intention of this article is to demonstrate how to import multiple files from a folder to a SQL Server table using DTS and Batch File. This batch file can also be scheduled as jobs. You can enhance the batch file by adding the functionality of accepting more parameters in the batch file.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved