使用DTS导入多个文件数据到SQL Server中

使用DTS导入多个文件数据到SQL Server中

    在我们工作中，经常需要将保存在文件中的数据导入到SQL Serve的表中。有时可能需要同时从相同或不相同的文件目录中导入多个文件的数据到SQL Server中。这里我们将讨论如何使用批处理文件和DTS从一个特定的文件目录中，导入多个文件的数据到SQLServer中。

    试验环境
　　我们先创建整个试验的环境。创建文件目录“C:/MyImport”，和三个文件a.csv、b.csv和c.csv，文件内容如下。同时，在SQL Server中创建一个表用来存放导入的数据。
　　　　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
　　　　
　　　　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

　　创键DTS
 　　1、在DTS中创建3个全局变量，FileName、ServerName和DatabaseName。
　　
　　
　　2、创建Text File (Source) 和SQL Server连接，并创建数据转换任务，如下图所示。
　　
　　3、设置数据转换的对应关系如下图。
　　

　　4、创建动态属性任务（dynamic tasks）：连接InputFile中，设置Catalog的值为全局变量DatabaseName，DataSource的值为全局变量FileName；连接SQLServer中，设置DataSource的值为全局变量ServerName。
　　
   
　　
　　5、增加一个“成功时”的工作流在动态属性任务和连接InputFile之间。
　　
　　6、如下图那样，创建一个执行SQL任务，来保存数据导入的记录。
　　SQL为INSERT INTO LogTable (Status) VALUES (?)
　　
　　
　　点击参数，来设置参数，设置参数1为全局变量FileName。
    
　　
　　7、增加一个“成功时”的工作流在连接SQLServer和执行SQL任务之间。
　　
　　8、将DTS包保存成结构化存储文件。你也可以保存在SQL Server中，但我们这里只讨论保存成结构化存储文件的方式。
　　

　　创建批处理文件
 　如下所示创建批处理文件C:/MyImport/Import.bat。
　　　　REM Type: Batch File
　　　　REM Created by: Digjim
　　　　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/myimportdts1.dts" -U importuser -P import –A "ServerName"="DIGJIM" -A "FileName"="c:/myimport/%%i" -A "DatabaseName"="Bank"

　　　　REM Rename all the files with "old" as suffix
　　　　ren c:/myimport/*.csv *.oldcsv

　　执行批处理文件
 　　当批处理文件被执行以后，他会在C:/MyImport目录下产生一个DirList.txt的文件，这个文件会包含所有C:/MyImport下扩展名为CSV的文件，这些文件名会和其他必须的参数一气传输给DTSrun.exe。在这个例子里，DirList.txt的内容如下：
　　　　C:/MyImport/DirList.txt
　　　　acsv
　　　　b.csv
　　　　c.csv
 　　注意，在批处理文件中，根据你自己的情况设置ServerName，FileName和DatabaseName参数。
 
　　结果
 　　现在你可以去你的数据库看结果，
 　　批处理输入的数据：

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

     在logtable中记录的Log。

1	c:/myimport/a.csv	2004-4-19 1:16
2	c:/myimport/b.csv	2004-4-19 1:16
3	c:/myimport/c.csv	2004-4-19 1:16

如果你把DTS包存储在SQL Server中，批处理文件就这样写：
     REM Type: Batch File
     REM Created by: Digjim
     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" -U importuser -P import –A "ServerName"="DIGJIM" -A "FileName"="c:/myimport/%%i" -A "DatabaseName"="Bank" 

     REM Rename all the files with "old" as suffix
     ren c:/myimport/*.csv *.oldcsv

 

参考:

使用T-SQL导入多个文件数据到SQL Server中