How to get the number of records exported

Question

How to get the number of records exported

Frans Tromp

SSC Journeyman

Points: 80
More actions
May 3, 2003 at 5:13 pm

#159935

Just created a DTS package to export data to text files. I would like to add the following functionality: write to a log file (using VBScript and fso object) the number of records that were actually exported.
To get number of exported records I probably have to use the DTS status variable: DTSTransformPhaseInfo.DestinationRowsComplete. Perhaps there is a simpler solution?
Any known samples that tackle this problem?
May 6, 2003 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

KeithDavies SSC Veteran Points: 287 More actions · Answer 1

I assume this is SQL Server 2000. One way is to set up global variables one for success and one for failed rows to count the rows. For each row successfully inserted add one to the RowsSucceeded variable - repeat for Failed - these are in the multiphase pump - Function InsertSuccessMain() and InsertFailureMain(). In the on pump complete phase you can write the values of the variables to a logging table using a lookup, T-SQL or ADODB recordset. You can then use that table as the basis of a report.

Regards

Keith Davies

IT Consultant

Regards

Keith Davies
IT Consultant

Frans Tromp SSC Journeyman Points: 80 More actions · Answer 2

Thanks for your reply!

At this moment I limit myself to creating DTS packages with the graphical interface of DTS Designer in SQL Server Enterprise Manager using Connections, Transform Data Tasks and VBScript Tasks.

I guess what you suggest requires creating a Visual Basic project?

Regards, Frans

elan_bush Newbie Points: 9 More actions · Answer 3

I just use an ExecuteSQL task. The SQL selects count(*) with the same WHERE clause as I used for the text file creation. This result is place into a global variable which is then used to write my log file.

Frans Tromp SSC Journeyman Points: 80 More actions · Answer 4

Thanks.

That's a solution that I can use! I added a SQL task (following the transform data task) that counts the records using the same query and setting a global variable. It works!

Is it also possible to get the record count from the transform data task directly (not using a count(*) query but by inspecting its properties after executing)using an ActiveX Script task?

Regards, Frans

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 5

But why run [essentially] the same query twice? How about if you do the following in your stored procedure?

Create Procedure usp_mysproc

@somevalue int = 1

AS

--Place your query here for example:

INSERT mytable(field1, field2) SELECT

field1, field2 from mytable2 where field1 = @somevalue

RETURN(@@ROWCOUNT)

GO

This way you run your query only once and return the count of affected rows through the RETURN parameter.

In your Execute SQL task, you can set the global variable equal to the return value of the sproc.

hth,

Michael

Michael Weiss