In have a DTS package involving Batch files, ActiveX vbs scripts, an
Access2000 databases and SQL Server Database. At the end of the package, I'm
using an 'Execute SQL Task' to create an email using XP_sendmail to notify
users that the access data base has been updated. Everything thing works
fine. Now, I would like to change the body of the message to include how
many records in the Access database were actually added. I'm having a
problem accessing the DTSGlobalVariables in the 'Execute SQL Task' which
seems like a logical place to hold a simple value of how many records were
added to a Access database in an eariler process. Any suggestions. Thanks
in advance.

Hello Bill,

So you may want to grab the amount of rows that were moved by assigning the
result of RowsInError/RowsComplete

http://doc.ddart.net/mssql/sql2000/h...ropmr_05wp.htm

to a Global variable

How are you assigning the values in the ExecuteSQL task.

Maybe using this article would be easier?

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi Allan,

Right now, my DTS package has a 'Execute SQL Task' with the following code:


DECLARE @BodyMessage varchar(100)
SET @BodyMessage = 'Number of Records Loaded: ' +
DTSGlobalVariables("iRecordCount").Value

EXEC xp_sendmail @recipients ='joe.smith',
@message = @BodyMessage,
@subject = 'New Records Loaded'


I realize I could just excute a direct SQL statement to the Access database
(and may end up doing that), but was hoping to use the DTSGlobalVariables,
which seems to be a more simple solution.




"Allan Mitchell" wrote:

Hello Bill,

You cannot use the GV in this way.

The article I posted from your site could be your best way of doing this.
The way you substitute parameters into statements is to place a ? in the
statement and then press the Parameters button and then map a GV to the parameter.

I still would favour the Article though because the design time is not particularly
robust.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com