Greetings,

I am trying get my head around this Transform Data Task regarding Error
Processing. What I have set up is a simple 3 row XLS spreadsheet with
column names as the first and 3 "records" to be imported as rows 2-4.

I have a package that goes like this:
Step1: ExecuteSQLTask - creates a staging table for the XLS import
Step2: (on step 1 success) TransformDataTask - loads that XLS data into the
staging table.

The staging table has more columns than the source XLS file does. I have
added indicators to the table so that I can produce a report later on as
well as give the users the ability to "fix" the rows which have
ScrubErrorInd = 'Y' set.

There is my problem: How can I set ScrubErrorInd to 'Y' * for the current
row being transformed * when one of the source columns meets the meets the
business condition(s) I have been given to determine a good scrub or not?
In this test case I have set up, I simply have a NULL value in the row and I
am testing for it in an ActiveX Script (see below). The default for
ScrubErrorInd is a 'N' set in the DDL when the table is created in step 1.

This package will, later on, insert all of the rows in the staged table that
scrub clean into the production table. The rows that have ScrubErrorInd =
'Y' will be left in the staging table and cleaned up manually by the users.
A job to load the cleaned rows into production will be run after they clean
the "dirty" rows in the staging table. This is a one-time process.

The package works great with a perfect source file and no error checking
but, of course, I cant leave it that way. So I am trying all sorts of error
processing ideas and this is what I came up with.....

Here is the activex script I have set up for the column I am testing looks
like this:

Function Main()
IF len(DTSSource("ModelCode")) = 0 THEN
DTSDestination("ModelCode") = DTSSource("ModelName")
DTSDestination("Phase1ScrubErrorInd") = "Y"
DTSDestination("Phase1ScrubErrorReason") = "Model Name Missing"
Main = DTSTransformStat_ExceptionRow
ELSE
DTSDestination("ModelCode") = DTSSource("ModelName")
Main = DTSTransformStat_OK
END IF
End Function

The error I am getting at the task level is:

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Invalid procedure call or argument: 'DTSSource'

Error on Line 1

Any ideas on what the error for line 1 would be? Or is that a generic error
line # and the error is something else? I am pulling what little remains
of my hair out!!

Any guidance would be greatly appreciated!!

Cheers,
Anthony

Well I think I got it! I sort of cheated though. I created the import
using the Import/Export Wizard and then saved it and now what I will do is
dive into it and figure out why and how it works.

Anthony

"ANTHONY" <reply_to (AT) newsgroups (DOT) only> wrote