用DTS导入文本文件时， 怎样跳过文本文件的第一行和最后一行

Suppose we have a table as follows:

CREATE TABLE [ignore_rows] (
 [c1] [int] NULL ,
 [c2] [char] (10)
)

And the text file is as follows:

1,aaa
2,bbb
3,ccc
100,ddd

To ignore the first and the last row of the text file when importing the text file to the table, you can use these steps:

1. In SQL Enterprise Manager, right click the Data Transformation Services, click New Package, this will launch the DTS package designer.

2. Click Package --> Properties menu, click the Global Variables tab, add two global variables:

currentRow , int, initial value 0  --> we use it to track the row we are currently processing.
lastRow, int, initial value 0         --> we use it to record the row number of the text file.

3. Add an ActiveX Script Task to the design pane, the script is as follows.

This script use the File System Object (FSO), for more information regarding FSO, please check it on MSDN.

Function Main()

Dim fso
Dim ts
Dim rowCount

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile("C:\data\ignore_rows.txt", 1)  @#1 for reading

rowCount = 0

While Not ts.AtEndOfStream
ts.SkipLine
rowCount = rowCount + 1
Wend

DTSGlobalVariables("currentRow").Value=0
DTSGlobalVariables("lastRow").Value=rowcount

Main = DTSTaskExecResult_Success

End Function

4. Drag two connections to the pane, one text file connection and one Microsoft OLE DB Provider for SQL Server connection, and then drag a Transform Data Task. The ActiveX transformation script is as follows:

Function Main()

DTSGlobalVariables("currentRow").Value=DTSGlobalVariables("currentRow").Value+1

@#The following code will skip and first row and last row
if DTSGlobalVariables("currentRow").Value=1 or DTSGlobalVariables("currentRow").Value=DTSGlobalVariables("lastRow").Value then
 Main=DTSTransformStat_SkipRow
else
 DTSDestination("c1") = DTSSource("Col001")
 DTSDestination("c2") = DTSSource("Col002")
 Main = DTSTransformStat_OK
end if

End Function

5. Set the precedence correctly, the final package is as follows:

ActiveX Script Task --(on success)--> Text file connection --(transform data)--> SQL connection

 

原文转自：http://www.ltesting.net