December 5, 2006 at 1:24 pm
Hi,
Can someone help me on this? I have a DTS package created which would dump the data into the excel file and then sends an email. But what i want to do is i want the email to be sent only if there is any data present in the excel file. If there is no data in the excel i dont want to send an email. can anyone provide an active x script or any other solution for this. Thanks a lot for any help.
December 6, 2006 at 4:18 am
you can use an "execute sql task"
use a select count(*) from tabletoexport
if count(*) > 0
xp_sendmail
end if
December 6, 2006 at 7:13 am
In the Workflow properties, you can execute an ActiveX script that determines whether to run the step or not. The task called "MyExcelTask" (use your name; find name in the disconnected edit) has to be a datapump task.
function Main()
if DTSGlobalVariable.Parent.Tasks("MyExcelTask").CustomTask.RowsComplete < 20 then
' 20 or some other threshold
Main = DTSStepScriptResult_DontExecuteTask
else
Main = DTSStepScriptResult_ExecuteTask
end if
end function
Russel Loski, MCSE Business Intelligence, Data Platform
December 6, 2006 at 7:43 am
Thank you so much for the replies. I will try those.
Thanks again
December 6, 2006 at 8:56 am
Hi
I tried both the options, the first one is giving an error when i include the attachment in the xp_sendmail
The second option is giving me the following error.
Error Source = Microsoft VB runtime error
Error Description: object required: 'DTS Global Variable'
Do i need to have any global variables created. Please help
December 6, 2006 at 9:08 am
It should be DTSGlobalVariables. I am not at my work machine with all of my DTS packages.
function Main()
if DTSGlobalVariables.Parent.Tasks("MyExcelTask").CustomTask.RowsComplete < 20 then
' 20 or some other threshold
Main = DTSStepScriptResult_DontExecuteTask
else
Main = DTSStepScriptResult_ExecuteTask
end if
end function
Russel Loski, MCSE Business Intelligence, Data Platform
December 6, 2006 at 9:10 am
are you sure the file to be attached was created?
which is the error??
December 6, 2006 at 9:13 am
Thanks for the quick response. sorry to bother but can you see this code
Function Main()
if DTSGlobalVariables.Parent.Tasks("DTSStep_DTSDataPumpTask_2").CustomTask.RowsComplete < 20 then
' 1 or some other threshold
Main = DTSStepScriptResult_DontExecuteTask
else
Main = DTSStepScriptResult_ExecuteTask
end if
end function
is the datapump task name correct. or will it be different. I opened the transform data task that is connecting the server to the excel and unser workflowproperties -> options->name (Got this name)
Please let me know. I am getting the error that the DTSStep_DTSDataPumpTask_2 is not found
Thanks a lot for your help
December 6, 2006 at 9:17 am
yes the file to be attached is present.
This is the code i am using
EXEC master.dbo.xp_sendmail @recipients = 'xxx@xxx.com',
@subject = 'Error Report',
@message = 'Please check the Errors in the report',
@attachments = C:\Documents and Settings\Desktop\test.xls'
when i execute just the first part with out attachments i am getting the email. but when i include attachment its giving me an error : xp_sendmail: failed with mail error 0x80004005
Thanks for your reply.
December 6, 2006 at 9:24 am
Be sure to get the name of the task not the step. They are different. "DTSStep_DTSDataPumpTask_2" looks like a default step name not a task name.
You can get the TaskName from the disconnected properties Step record (it is just off the screen in the step properties).
Russel Loski, MCSE Business Intelligence, Data Platform
December 6, 2006 at 9:24 am
the step executed successfully. The name should be DTStask_DTSDataPumpTask_2. Thanks a lot for your help.
December 6, 2006 at 9:29 am
Hi Luciano,
The other solution worked but just curios to know as to why the xpsend_mail errored out when i run with attachments? any idea. Did i mention anything wrong in the query. Thanks
December 6, 2006 at 9:40 am
Hi sorry to get back again. but i see a problem here. In my dts package i have few other steps that needs to be performed after i send the email. What i am doing is pulling the records from a csv file to a sql table and validating those records. if that table has got errors it would dump those errors into the excel and then i have a send mail task that sends an email. later i have to pull all the correct records into another table. The problem is the active x script is failing the whole package. is there any way that it doesnt run the next steps when there are no errored records.
Please help
December 6, 2006 at 9:41 am
it works without the attachment?
December 6, 2006 at 9:43 am
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply