Import Multiple Files to SQL Server Using DTS - Page 2

Assign parameter value

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.

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.

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.

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

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