Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 24, 2004

Import Multiple Files to SQL Server Using DTS - Page 2

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date