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, AAA2, BBB3, CCC100, DDD
To ignore the first file when ignial 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 FSODIM TSDIM ROWCOUNT
SET FSO = CreateObject ("scripting.filesystemObject") SET TS = fso.opentextfile ("c: /data/ignore_rows.txt", 1) '1 forreading
Rowcount = 0
While not ts.atendofstreamts.skiplinerowcount = rowcount 1Wend
DTSGLOBALVARIABLES ("currentrow"). Value = 0dtsglobalVariables ("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 The Drag a Transform Data Task. The ActiveX Transformation Script IS FOLLOWS:
Function main ()
DTSGLOBALVARIABLES ("currentrow"). Value = DTSGLOBALVARIABLES ("currentrow"). Value 1
'The following code will skip and first row and last rowif DTSGlobalVariables ( "currentRow"). Value = 1 or DTSGlobalVariables ( "currentRow"). Value = DTSGlobalVariables ( "lastRow"). Value then Main = DTSTransformStat_SkipRowelse DTSDestination ( "c1") = DTSSOURCE ("COL001") DTSDestination ("C2") = dtssource ("col002") main = DTSTRANSFORMSTAT_OKEND IFEND FUNCTION
5. Set The Precedence Correctly, The Final Package IS FOLLOWS:
ActiveX Script Task - (on success) -> Text File Connection - (Transform Data) -> SQL Connection
