Import multiple file data into SQL Server using DTS
In our work, it is often necessary to import the data stored in the file into the table of SQL Serve. Sometimes you may need to import data from multiple files in the same or different file directory to SQL Server. Here we will discuss how to use batch files and DTS from a specific file directory to import multiple files into SQLServer.
Test Environment We first create the environment of the entire trial. Create a file directory "C: / myimport", and three file a.csv, b.csv and c.csv, the file content is as follows. At the same time, create a table in SQL Server to store imported data. 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: 8411992710, 3052713, 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
Create DTS 1, create 3 global variables in DTS, FileName, ServerName, and DatabaseName. 2. Create a text file (source) and SQL Server connections, and create a data conversion task, as shown below. 3. Setting the correspondence between data conversion as shown below. 4. Create a dynamic task (Dynamic Tasks): Connect to InputFile, set the value of Catalog DatabaseName, DataSource's value for global variable filename; Connect to SQL Server, set the value of DataSource as global variable servername. 5. Add a "successful" workflow in dynamic attribute tasks and connects between inputfile. 6, as shown below, create a record of executing SQL tasks to save data import. SQL is the INSERT INTO LOGTABLE (?) To set parameters, set parameters, set parameters 1 for global variables FileName. 7. Add a "successful" workflow between connecting SQL Server and performs SQL tasks. 8, save the DTS package into structured storage files. You can also save in SQL Server, but we only discuss how to save the structured storage file here. Create a batch file Create a batch file C: /MYIMPORT/Import.bat as shown below. 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
After executing a batch file When the batch file is executed, he will generate a Dirlist.txt file in the C: / MyImport directory, this file will contain all C: / MyImport extensions as CSV files, these file names And other must have a gas transfer to DTSRun.exe. In this example, the contents of Dirlist.txt are as follows: c: /myimport/dirlist.txt ACSV B.CSV C.CSV Note, in the batch file, set the ServerName, FileName, and DatabaseName parameters according to your own situation. As a result, now you can go to your database to see the results, batch input data: 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
twenty two
Mike
9418952711
4000.56
39
HSU
75611792511
1230
69
Lucy
8411992710
305.11
45
GRACE
3413452713
246.52
33
Saint
5461795716
1278.7
Log recorded in LogTable.
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
If you store the DTS package in SQL Server, you will write this: Rem type: Batch file Rem create 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 reference:
Import multiple file data into SQL Server using T-SQL
