Logging in DTS

Question

Logging in DTS

gv_pradeep

Valued Member

Points: 71
More actions
August 19, 2008 at 7:51 am

#71777

Hi,
I have a DTS where I have one Execute SQL Task.
I need to log the number of rows affected by this SQL task.
Logging Tab in Package Properties gives me very limited options.
Please let me know how i can achieve this.
Thanks.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Try adding a global variable to the package then setting it to @@ROWCOUNT in the Execute SQL task. You could then write that out to a table or file.

Greg

gv_pradeep Valued Member Points: 71 More actions · Answer 2

Thanks for the response.

I'm not very sure how to write a global variable into a file either. It's a start though.

It would be great if you could provide me with a good link on the same.

Thanks again

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

From BOL:

How to save row values into global variables (Enterprise Manager)

To save row values into global variables

From the Connection toolbar, drag a Microsoft® OLE DB Provider for SQL Server connection to the Data Transformation Services (DTS) design sheet.

In the Connection Properties dialog box, in the Database list, click pubs.

From the Task toolbar, drag an Execute SQL task to the design sheet.

In the Execute SQL Task Properties dialog box, in the Existing connection list, click the pubs connection just created.

In the SQL statement box, type the SQL code. For example:

SELECT *

FROM titleauthor

WHERE (royaltyper = '40')

Click Parameters, click Create Global Variables, and then enter the global variable names. For example: o_au_id, o_title_id, o_au_order, and o_royaltyper.

Click the Output Parameters tab, click Row Value, and in the Output Global Variables column, click a row and select the global variable from the list to hold the column's data.

You can skip a column when saving values to a global variable. For example, if you do not want to store the value of the title_id column, modify the Output Global Variable column to assign the title_id column to .

You could then use a Transform Data task to select and export the value of the global variable to a table.

Greg

gv_pradeep Valued Member Points: 71 More actions · Answer 4

Hi Greg,

In the Execute SQL Task Properties dialog box, when i establish the right existing connection and click on Parameters button, it pops a message saying that

'The SQL Statement does not contain any parameters'. It doesn't let me create one.!

I'm using SQL Server 2000.

gv_pradeep Valued Member Points: 71 More actions · Answer 5

Hi Greg,

I found out why that message was popping on click of parameters.

I had to add

SELECT @@ROWCOUNT AS RowsAffected

Thanks for your help 🙂