DTS How to...Skip Rows during Import
September 24, 2000
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...
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:
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: