no. of rows copied after a Transform Data Task? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

no. of rows copied after a Transform Data Task?

Author

Topic

AlfieNoakes
Starting Member

14 Posts

Posted - 2004-09-22 : 09:24:27

How, in my DTS Package, can I obtain the number of rows copied from a Transform Data task, and load them into a global variable?

Thanks in advance!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-22 : 09:42:04

You could do it via an ActiveX Script using the RowsComplete property of the transform data task (create a global variable called gv_RowsComplete and replace "My Transform Data Task Name" with the name of the task in your package):

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
	Dim objPkg
	Dim objTask
	Dim objPump
'create objects
	Set objPkg = DTSGlobalVariables.Parent
	Set objTask = objPkg.Tasks("My Transform Data Task Name")
	Set objPump = objTask.CustomTask
'initialise global variable
	DTSGlobalVariables("gv_RowsComplete").Value =  objPump.RowsComplete

'destroy objects
	Set objPump = Nothing
	Set objTask = Nothing
	Set objPkg = Nothing
'return success
	Main = DTSTaskExecResult_Success
End Function

However, depending on the source of your transformation, it may be easier to use an execute SQL task

Mark

AlfieNoakes
Starting Member

14 Posts

Posted - 2004-09-22 : 11:02:26

Mark, I've applied your code to my DTS Package, as follows:

Dim objPkg
Dim objTask
Dim objPump

'create objects
Set objPkg = DTSGlobalVariables.Parent
Set objTask = objPkg.Tasks("DTSStep_DTSDataPumpTask_1")
Set objPump = objTask.CustomTask

'initialise global variable
DTSGlobalVariables("intRowsAppendedToCDRTable").Value = objPump.RowsComplete

'destroy objects
Set objPump = Nothing
Set objTask = Nothing
Set objPkg = Nothing

'return success
Main = DTSTaskExecResult_Success

--------------------------------------------------------------------------------------------

However, if when I run the DTS Package, I get this error:
Task "DTSStep_DTSDataPumpTask_1" not found

I think it's because the transformation task is actually a step. For example, this would work:
set stpImportDCRFile = Objpkg.Steps("DTSStep_DTSDataPumpTask_1")

Any ideas?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-09-22 : 11:24:53

It looks like you're referencing the step associated with that task rather than the task itself. Replace "DTSStep_DTSDataPumpTask_1" with "DTSTask_DTSDataPumpTask_1"

Mark

AlfieNoakes
Starting Member

14 Posts

Posted - 2004-09-22 : 11:36:39

Thank you, that fixed the problem!

ramakanth_gupta
Starting Member

5 Posts

Posted - 2004-09-24 : 03:06:31

choose the data transformation as insert success and declare a global variable (for row count).

choose activex task in dts_transformation

in the activeX task u just increment the global variable
then after transformation u will get the no of rows transformed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources