email notification of # of records
-
I've written a DTS package that takes certain data from database 1 to populate database 2 and it is working successfully.
I have set up email notification to myself to let me know the package executed successfully.
I would like to also include in the email how many records were added to database 2. Is there a way I can do this?
-
This would be a good candidate for an ActiveX Script:
http://www.sqldts.com/default.aspx?235
Also, you want to put the number of records in a global variable and reference that global variable in the ActiveX.
-
Thank you. This does make sence, but my experience is limited.
I have 2 connections, a transform data task and a mail task in the DTS package.
Do I add an activex task to the package?
I am not sure where or how to set up the global variable.
-
I've tried to do this in DTS. Unless you really want to learn a lot about VBA its not worth the trouble. Its much easier to use xp_sendmail. Create a stored proc that calls it and then create a task in your DTS package that executes it. The mail object in DTS is ok as long as you just want a static message sent. Sending dymanic data from SQL server is not easy.
-
The dts should be set up this way:
Connection1--->Connection2--->ActiveX--->Send Mail Task
And this is what should be in the ActiveX Script:
Function Main()
dim conn, rs
set conn = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.Recordset")
DSN1 = "Provider=sqloledb;" & _
"Data Source=TRICAST2;" & _
"Initial Catalog=Quote;" & _
"Integrated Security=SSPI"
Conn.Open DSN1
set rs=conn.execute("Select count(*) from test_table")
DTSGlobalVariables("gvtest").Value=rs(0)
Dim oPkg, oTasks, oSendMailTask
' Get Package object
Set oPkg = DTSGlobalVariables.Parent
' Get Tasks collection
Set oTasks = oPkg.Tasks
' Get DTS Send Mail Task by Name
Set oSendMailTask = oTasks("DTSTask_DTSSendMailTask_1").CustomTask
' Set Subject to the Package Name
oSendMailTask.Subject = oPkg.Name
' Set Message Text, including a global variable value
oSendMailTask.MessageText = "Dummy Text." & vbCrLf & _
"MyGlobalVariable Value :" & DTSGlobalVariables("gvtest").Value
' Set the attachement to the file of the named connection
'oSendMailTask.FileAttachments = oPkg.Connections("Text File (Destination)").DataSource
' Clean Up
Set oSendMailTask = Nothing
Set oTasks = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
*******************************************************
Substitute ServerName and DatabaseName for the connectionstring and TableName for Connection2 destination table.
Also, use this for windows authentication:
DSN1 = "Provider=sqloledb;" & _
"Data Source=TRICAST2;" & _
"Initial Catalog=Quote;" & _
"Integrated Security=SSPI"
this for sql authentication:
DSN1 = "Provider=sqloledb;" & _
"Data Source=TRICAST2;" & _
"Initial Catalog=Quote;" & _
"uid=username;pwd=password"
-
Like I said, its a lot easier to do this in SQL Server. First the code above won't return the proper data. You don't want a row count you want the value of the @@rowcount global variable immediately after you execute the insert statement.
The T-SQL code to do this looks something like this:
exec xp_startmail (just to make sure its running)
declare @newrows varchar(5)
Your insert statement (insert into t2 select * from t1 )
select @newrows = convert(varchar(5),@@rowcount)
exec xp_sendmail @recipients = 'you@servername; yourboss@servename,
@subject = 'Process Completed',
@message = 'Number of rows inserted: ' + @newrows
That's it. DBAs sometimes complain about setting up the server to use xp_sendmail, but hey, thats what they get paid for.
-
Thanks for your reply.
I have created a DDQ that counts the records.
The transformation is set to Write File.
I have the correct directory set in the Write File Transformatin Properties,
the file type is ANSI,
the File name column is specified and
Handle existing file is set to "Overwrite if file exists"
However, the file is not being created.
I tried to work around by creating the file in the directroy and setting Handle existing file to overwrite, but it does not overwrite.
Any feedback?
Thanks
> Keep It Simple Soulutions
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply