I have a DTS that does a Data transform, copy columns from text to table, and I would like to keeps track of the number of records that failed and store it in a global variable.

I then call the global variable from another dts packags.

I want to keep track of num of records that are succesful and the num of records that failed in the data transformation.

Any ideas how I can do this.

In article <DC9A16EF-2089-4DE7-BD52-259D1C19557A (AT) microsoft (DOT) com>, kdabda
<anonymous (AT) discussions (DOT) microsoft.com> writes
To allow more than one row to fail you must first set the Max Errors
count property on your DataPump task to something higher than 1.

You can then use an ActiveX Script Task to reference the DataPump Task
and use the RowsComplete and RowsInError, like this-

Dim sMsg
Dim oTask
Dim oDataPump

Set oTask = DTSGlobalVariables.Parent.Tasks("DTSTask_DataPump_ 1")
Set oDataPump = oTask.CustomTask

DTSGlobalVariable("GoodRows").Value = oDataPump.RowsComplete -
oDataPump.RowsInError

DTSGlobalVariable("BadRows").Value = oDataPump.RowsComplete -
oDataPump.RowsInError

DTSGlobalVariable("TotalRows").Value = oDataPump.RowsComplete

Set oDataPump = Nothing
Set oTask = Nothing


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Sweet, I will give it a try tommorow. Thanks!

Thanks Darren,

I have the name of my tranform task "DTSStep_DTSDataPumpTask_1" But it comes up with "DTS task 'DTSStep_DTSDataPumpTask_1' was not found. I see in the below exception report it calls it a step: Am I not calling the correct thing? Or could I have my 'Transform Data Task' set up incorrectly? The transform data task step runs fine. Thanks for the help!

DATA TRANSFORMATION SERVICES: Data Pump Exception Log
Package Name: TransferFromText
Package Description: (null)
Package ID: {27D8D920-E52C-493B-A3CD-C6071881EA65}
Package Version: {0895831F-C0DC-4827-99A1-BFCC0CF0DD72}
Step Name: DTSStep_DTSDataPumpTask_1
Execution Started: 12/11/2003 8:43:36 AM
Execution Completed: 12/11/2003 8:43:36 AM

In article <A72A1FC8-2B73-493A-8C19-4736A2649825 (AT) microsoft (DOT) com>, kdabda
<anonymous (AT) discussions (DOT) microsoft.com> writes
In DTS you have a step and a task, two different objects, but related
together one to one.

To check the name of your task use Disconnected Edit, and make sure you
look at the Tasks collection, reading off the Name property. This of
course must be set in the script.

Make sense?
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren

Here is my code belo

DTSGlobalVariables("GoodRows").Value = oDataPump.RowsComplete - oDataPump.RowsComplet
msgbox DTSGlobalVariables("GoodRows").Value
DTSGlobalVariables("BadRows").Value = oDataPump.RowsComplete - oDataPump.RowsInErro
msgbox DTSGlobalVariables("BadRows").Value
DTSGlobalVariables("TotalRows").Value = oDataPump.RowsComplet
msgbox DTSGlobalVariables("TotalRows").Value

The GoodRows come up 0, then BadRows comes up 852, and TotalRows comes up 852. But there are 852 rows that got transferred in the database. So it is like opposite. Any ideas. Thanks,

In article <FB360E16-F64D-4E9F-9227-6703A5A2BABF (AT) microsoft (DOT) com>, kdabda
<anonymous (AT) discussions (DOT) microsoft.com> writes
Sorry, some typos by us both in there, try-

DTSGlobalVariables("GoodRows").Value = oDataPump.RowsComplete -
oDataPump.RowsInError

msgbox DTSGlobalVariables("GoodRows").Value


DTSGlobalVariables("BadRows").Value = oDataPump.RowsInError

msgbox DTSGlobalVariables("BadRows").Value


DTSGlobalVariables("TotalRows").Value = oDataPump.RowsComplete
msgbox
DTSGlobalVariables("TotalRows").Value


RowsComeplete is all rows transferred, so this is total. RowsInError is
the failed rows, so the good rows is the total less the errors. I just
managed to get them the wrong way round. Sorry

Cheers

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Excellent! I even read what Microsoft had out there for getting these I could not see that they were mixed up. Thanks for the response!! Working perfectly thanks for all the help!!