How to Log skipped rows

Question

How to Log skipped rows

svv_laser

Grasshopper

Points: 21
More actions
October 18, 2008 at 8:47 am

#77114

Hi I have requirement in which i need to log skipped rows by DTSTransformstat_SkipRow function
to a text file. Can any one help me. Thanks in advance.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 1

I can think of 2 ways of doing this, I'm sure there are others.

First way would be to change the DTSTransformStat_SkipRow constant in your Active X transformation script task to DTSTransformSTat_ExceptionRow. In the Transformation Properties window, go to the Options tab and enter a file path in the Exception File Name box. Under the Data Movement section of this tab, change the value of Max Error Count from 0 to 9999 (the max value). When your tranformation runs, the good records will be moved to your destination and the bad records along with an error description will be written to the specified exception file. There are problems with this solution. It may result in your DTS Package returning failure and if there are more than 9999 exceptions, I'm not sure if all of them would be written into the exception file.

Here's a better way. First, create your normal data transformation that will handle only the good records. Create a second transformation that uses a Text File destination. Transfer the same data as in your initial transformation, but change your Active X Script task so it only looks at the records that would have been skipped. For example, let's say you have a text file. You want to import the records that have a value in the TotalCost column into a SQL table and all the records will a NULL value in the TotalCost column into a text file. Create a transformation with a SQL destination and in the Active X Script task for the TotalCost column, add the following code:

Function Main()

If IsNull(DTSSource("TotalCost")) Then

Main = DTSTransformStat_SkipRow

Else

DTSDestination("TotalCost") = DTSSource("TotalCost")

Main = DTSTransformStat_OK

End If

End Function

Add a second transformation with a text file destination (using the same source connection as your other transformation) and add this code to the Active X script task for the TotalCost column: