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
