Skipping Rows With Bad Data

#1

As I process a text file, I need skip rows that that have missing data.
I added the following row transformation script, but it causes every
row to be skipped.

Function Main()
Dim missingFields
Main = DTSTransformStat_OK
missingFields = ""

if IsNull(DTSSource("Address1")) or trim(DTSSource("Address1").value)
= "" then
missingFields = missingFields + "Address1"
Main = DTSTransformStat_SkipInsert
end if

End Function

I've also tried setting Main = DTSTransformStat_SkipRow.

It looks like what's happening is that if one row get's skipped, all
the rows get rolled back.
But I've turned off transactions and still don't get any data.

Any ideas would be greatly appreciated.

#2

Hello BradR,

Quote:

As I process a text file, I need skip rows that that have missing
data. I added the following row transformation script, but it causes
every row to be skipped.

Function Main()
Dim missingFields
Main = DTSTransformStat_OK
missingFields = ""
if IsNull(DTSSource("Address1")) or trim(DTSSource("Address1").value)
= "" then
missingFields = missingFields + "Address1"
Main = DTSTransformStat_SkipInsert
end if
End Function

I've also tried setting Main = DTSTransformStat_SkipRow.

It looks like what's happening is that if one row get's skipped, all
the rows get rolled back.
But I've turned off transactions and still don't get any data.
Any ideas would be greatly appreciated.

Is this the only transformation you have in your datapump and the script
you use is precisely like the one you wrote in your post?

#3

This code is actually OK. It turned out I messed something up in the
next step.

I did end up putting all row transformations in 1 script.

Davide wrote:

Quote:

Hello BradR,

As I process a text file, I need skip rows that that have missing
data. I added the following row transformation script, but it causes
every row to be skipped.

Function Main()
Dim missingFields
Main = DTSTransformStat_OK
missingFields = ""
if IsNull(DTSSource("Address1")) or trim(DTSSource("Address1").value)
= "" then
missingFields = missingFields + "Address1"
Main = DTSTransformStat_SkipInsert
end if
End Function

I've also tried setting Main = DTSTransformStat_SkipRow.

It looks like what's happening is that if one row get's skipped, all
the rows get rolled back.
But I've turned off transactions and still don't get any data.
Any ideas would be greatly appreciated.

Is this the only transformation you have in your datapump and the script
you use is precisely like the one you wrote in your post?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Skipping Rows With Bad Data

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Skipping Rows With Bad Data - 08-08-2006 , 08:32 PM

Re: Skipping Rows With Bad Data - 08-09-2006 , 04:12 AM

Re: Skipping Rows With Bad Data - 08-09-2006 , 10:22 AM