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.
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