I am trying to log the row transform phase to a text file while running
the copycolumn type for the phase. The problem is that if you set the
row transform phase filter to copycolumn - I can't find a way to write
out to the text file that they are there and what row they are working on.

I have the transformation script for all the other phases writing to the
same text file. But I can't set the row transform phase to use the
transformation script if I set it to copycolumn.

What is happening is that I have a progress database that I am trying to
move to my Sql Server 2000 database. This will be done every night.
The problem is that we have about 13 files that get errors from the
Merant ODBC driver that says that some of the rows have a value
exceeding its max length or precision. These fields are all x(1000).
The program using the database doesn't seem to have a problem, so I
assume there is a problem with the ODBC driver. It makes no sense about
the error as I am reading the data not writing the data - so what has
the length got to do with anything.

I am trying to figure out which rows have these problems and by tracing
each phase, I hope to figure out what the problem is.

Thanks,

Tom.

Does sound like a Driver problem.

What happens if you set your fetch size to 1 and your insert size to 1. You
then set your errors up high. By comparing then the rows you have in SQL
Server with the rows in the Progress DB you can see the ones that come
across.

also in 2000 there is the opportunity to log failed rows to a text file
(last tab DataPump task.)

Try doing the import using and into MS Access. Does that work?

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Thomas Scheiderich" <tfs (AT) deltanet (DOT) com> wrote

Allan Mitchell wrote:

I already have found where my errors are and they seem to be in the
Progress database. If you use their Sql tool, they get the same error,
that says the size of the field is greater than the defined size. What
I was trying to do was send a line to a text file from each Phase to see
what it was doing. It works fine for all the Phase, except the Row
Transform Function - which I cannot choose or I will lose the CopyColumn
function I need it to do.

The activeX script I have is:
************************************************** ********************
dim objFSO
dim objStream

const OUTPUT_FILE = "c:\salesreport.txt"
const fsoForWriting = 2

set objFSO = CreateObject("Scripting.FileSystemObject")

set objStream = objFSO.OpenTextFile(OUTPUT_FILE, fsoForWriting, true)

Function PreSourceMain()
objStream.WriteLine("Inside PreSourceMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
PreSourceMain = DTSTransformstat_OK
End Function

Function InsertSuccessMain()
objStream.WriteLine("Inside InsertSuccessMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
InsertSuccessMain = DTSTransformstat_OK
End Function

Function InsertFailureMain()
objStream.WriteLine("Inside InsertFailureMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
InsertFailureMain = DTSTransformstat_OK
End Function

Function TransformFailureMain()
objStream.WriteLine("Inside InsertFailureMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
TransformFailureMain = DTSTransformstat_OK
End Function

Function BatchCompleteMain()
objStream.WriteLine("Inside BatchCompleteMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
BatchCompleteMain = DTSTransformstat_OK
End Function

Function PumpCompleteMain()
objStream.WriteLine("Inside PumpCompleteMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
PumpCompleteMain = DTSTransformstat_OK
End Function

Function PostSourceDataMain()
objStream.WriteLine("Inside PostSourceDataMain for Row: " &
DTSTransformPhaseInfo.CurrentSourceRow)
PostSourceDataMain = DTSTransformstat_OK
End Function
************************************************** ********************

Here is the results:

************************************************** ********************
Inside PreSourceMain for Row: 0
Inside InsertSuccessMain for Row: 1
Inside InsertSuccessMain for Row: 2
Inside InsertSuccessMain for Row: 3
Inside InsertSuccessMain for Row: 4
Inside InsertSuccessMain for Row: 6
Inside InsertSuccessMain for Row: 7
Inside InsertSuccessMain for Row: 8
Inside InsertSuccessMain for Row: 9
Inside PostSourceDataMain for Row: 69
Inside BatchCompleteMain for Row: 69
Inside PumpCompleteMain for Row: 69
************************************************** ********************

The problem is I cannot put a trace line for the Row Transform Function.
I did find that when I get my error from Progress, I never go to any
of the functions. I was trying to trap it, but apparently the error
doesn't go to the Insert or Transform error functions. I want to be
able to send a record to an SQL table if there is an error, but as you
can see from the results (row 5 is missing), it doesn't do anything
different with the error.

Tom.