DTS How to…Skip Rows during Import

7 How to Skip Rows during Import

A common request is how to skip certain rows during an import or data transformation process.
DTS can accomplish this, but perhaps the simplest solution is to import data
into a staging table, then use standard SQL statements to delete what is not required,
before inserting the remaining rows into the final table.

Now back to DTS…
Firstly you can skip header rows by using the First Row property of the DataPump Task, see Advanced properties sheet.
The Text File Connection also has a Skip Rows property, see file Properties of the Connection.

For more advanced situations you can control what is actually inserted by using an
ActiveX Transformation Script. The default transformation script always ends by
setting the function result to DTSTransformStat_OK. This is one of the transformation return codes, or the DTSTransformStatus constants, which can be used to control the transformations behaviour, such as skipping an insert.

To prevent the current rows from being inserted, we return DTSTransformStat_SkipInsert.

The following example will skip the insert because our first column is blank,
and for this example is assumed to be our primary key:

Function Main()
	If IsNull(DTSSource("Col001")) Then
		Main  = DTSTransformStat_SkipRow
	Else
		DTSDestination("RowID") = DTSSource("Col001")
		DTSDestination("Name") = DTSSource("Col002")
		DTSDestination("Description") = DTSSource("Col003")
		Main = DTSTransformStat_OK
	End If
End Function

This example is used to restrict which rows are imported based on a value.
This is useful when the source is text file, as you cannot use a query to
filter the source rows as you would with an OLE-DB/ODBC compliant data source:

Function Main()
	If DTSSource("Col001") > 4 Then
		Main  = DTSTransformStat_SkipRow
	Else
		DTSDestination("RowID") = DTSSource("Col001")
		DTSDestination("Name") = DTSSource("Col002")
		DTSDestination("Description") = DTSSource("Col003")
		Main = DTSTransformStat_OK
	End If
End Function