February 12, 2007 at 10:34 am
I am on my last portion of my DTS which involves a bunch of stuff for importing records from a file. One of the steps is checking for a file in a folder.
If the file is present...process
These Steps are all finished and working.
If the file is not present....don't go forward.
This check is done via an ActiveX script I found on SQLDTS.com that checks a for a file in a folder. T
http://www.sqldts.com/211.aspx
he activeX script is placed in the Workflow properties and has a result of Execute or DontExecute based on if the file is there.
This part works too.
HOWEVER, I have an SQLTask that sends out an XP_SENDMAIL stating that a file is not present (more for notification)...
I was hoping to figure out how to kick off that specific task via ActiveX.
I think I am close but it isn't working...if someone could help explain how to get it execute a specific stand alone task I would appreciate it.
Here is my Code so far...which identifies the correct Task (via messagebox popup)...but like I said I cannot for the life of me figure out how to get it to execute it so it actually sends off the mail.
Any help is appreciated.
Leeland
----------------------------------------------------------------------------------------------------------
Option Explicit
Function Main()
Dim oPkg
Dim oStep
' Get reference to the Package
'------------------------------------------------
Set oPkg = DTSGlobalVariables.Parent
' Used to send an email that no file(s) were downloaded
'--------------------------------------------------------------------------------------
Set oStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_4")
msgbox(oStep.description)
' Set the status back to waiting
'------------------------------------------------
oStep.ExecutionStatus = DTSStepExecStat_Waiting
' Clean Up
'----------------
Set oStep = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
February 12, 2007 at 11:23 am
I find that I have to disable the step (workflow, disable), then I re-enable in code:
Option Explicit
Function Main()
Dim oPkg
Dim oStep
' Get reference to the Package
'------------------------------------------------
Set oPkg = DTSGlobalVariables.Parent
' Used to send an email that no file(s) were downloaded
'--------------------------------------------------------------------------------------
Set oStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_4")
msgbox(oStep.description)
' Set the status back to waiting
'------------------------------------------------
' ***************************
' This is what works for me
oStep.DisableStep = false
oStep.ExecutionStatus = DTSStepExecStat_Waiting
' Clean Up
'----------------
Set oStep = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
Russel Loski, MCSE Business Intelligence, Data Platform
February 12, 2007 at 12:15 pm
Maybe I am missing something. I did like you said above, went to the task, right-clicked -->workflow properties --> options and disabled the step...then added the code. It did not seem to work (Didn't get the xp_sendmail)
I don't know enough about ActiveX script to know all the in's and out's so that is probably where I am messing up.
To re-state the above:
I have a full DTS package that works, with one task that is not connected to anything (my SQL Task to send off an email).
In one of the steps of my DTS (ActiveX Script), it does a file count within a specified folder. In that ActiveX script step, within the Workflow Properties --> Options
there is a option to Use ActiveX Script, which I am using to check to make sure a file is even present. The reason I am doing this is because of the DTS_succeed DTS_failure. I don't want the package to fail simply because I don't have a file there.
Based on the result of the folder.size (to see if any files are in a folder) determines if that step gets run to get the count. ( I hope this makes sense).
It is within that ActiveX script that I want to be able to call that stand alone SQL task to send out an email to myself saying there are no records present.
Hope this clears up what I am looking to do and if what I have is written incorrectly...
Thanks,
Lee
February 12, 2007 at 2:20 pm
The message box will display if you run the task interactively. In a job, I believe it will stop the activex script from running.
Russel Loski, MCSE Business Intelligence, Data Platform
February 12, 2007 at 2:58 pm
You were right...I think. The task ran by itself would just display the SQL tasks description...and nothing else. What I did to get it to work is embed the code within that ActiveX Workflow Properties with a series of Functions. Here is the code that determines if the step will run or not.
I am still having one issue...when I have this stand alone SQL task sitting there it executes unless it is disabled (Which is what you showed me). SO I have it disabled and run the process listed below which re-enables the step...runs it and attempts to then disable it again.
HOWEVER, if I run it how it is coded below...it seems like it enables it and disables the step before it even runs...(i.e. it doesn't send out the mail.)
Is there a better way to enable a step -- run it -- and disable it again?
Otherwise if I leave it enabled, it sends out two emails vs. one.
Thanks for the hints I appreciate it!
Leeland
'===================================================================
'Returns a Value of Run or Don'tRun based on the response of the Execute Function.
'===================================================================
FUNCTION Main()
IF Execute = False Then
Main = DTSStepScriptResult_DontExecuteTask
ELSE
Main = DTSStepScriptResult_ExecuteTask
END IF
END FUNCTION
'=================================================================================
'The Execute Fuction Checks the folder size of the File_In_Temp directory which is used for temporary
'Storage of the files after they are downloaded from FTP site. If no files are present after the FTP
'download runs, it is assumed that no files are available and the "Find File Count" step should not be run
'and the package should stop.
'To Indicate the package will not continue an email is sent out notifying the ADMIN that the process ran
'but no files were present. The mail is sent out via the DTS SQL Task "No Files Downloaded" which gets
'called from the below FUNCTION SendMail.
'=================================================================================
FUNCTION Execute()
Dim FileSys
Dim Temp_Folder_Size
Dim Temp_FolderPath
Dim Temp_FolderSize
'------------------------------------
'Creating the File Object
'------------------------------------
Set FileSys = CreateObject("Scripting.FileSystemObject")
'------------------------------------------------------------------------
'Assigning the File Object to the Folder Property
'-------------------------------------------------------------------------
SET Temp_FolderSize = FileSys.GetFolder("\\ds06702\e$\nts\File_In_Temp")
'------------------------------------------------------------------------------------
'Checks the Folder size and assigns it to the variable
'------------------------------------------------------------------------------------
Temp_Folder_Size = (Temp_FolderSize.size)
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Checks the Temp folder size, if it is empty then the no file was successfully downloaded and should report a failure.
'----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF Temp_Folder_Size = 0 Then
Execute = False
SendMail()
ELSE
Execute = True
END IF
END FUNCTION
'=================================================================================
'The SendMail function executes the "No Files Downloaded" SQL Task which uses XP_SENDMAIL
'to send an email to the ADMIN that the process ran and no files were downloaded.
'=================================================================================
FUNCTION SendMail()
Dim oPkg
Dim oStep
'------------------------------------------------
' Get reference to the Package
'------------------------------------------------
Set oPkg = DTSGlobalVariables.Parent
'--------------------------------------------------------------------------------------
' Used to send an email that no file(s) were downloaded
'--------------------------------------------------------------------------------------
Set oStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_4")
'------------------------------------------------
' Set the status back to waiting
'------------------------------------------------
oStep.DisableStep = False
oStep.ExecutionStatus = DTSStepExecStat_Waiting
oStep.DisableStep = TRUE
'----------------
' Clean Up
'----------------
Set oStep = Nothing
Set oPkg = Nothing
END FUNCTION
February 12, 2007 at 3:41 pm
Why do you need to disable it? Once the task runs once, it will not start running again, until you run:
oStep.ExecutionStatus = DTSStepExecStat_Waiting
Once it runs, it acts like it has been disabled. The only way to wake it it the preceding code.
You can have a final script to disable it, if you are concerned with leaving it enabled.
Another way is to have a workflow started by the send mail and ended with an activex script that disables the send mail step.
Russel Loski, MCSE Business Intelligence, Data Platform
February 13, 2007 at 8:46 am
OK maybe I am looking at this wrong...I have the entire package connected except this one task. If I right-click and disable that single task and use the code to un-disable it before running the task (oStep.DisableStep = False) it works fine...for one time. Because after that inital run, the task has been enabled and run.
Once it runs for that inital time, the task stays enabled...so if I were to kick off the DTS package again to simulate the job running during the day, it will not find a file (then send an email based on the ActiveX script) and then it will send another email because that Task gets run again by itself
thus I get two emails when I should only get one.
I thought that if I had it disabled....then via code enabled it...ran the task (send email)...and then disabled it, I would be all set...However that doesn't seem to be the case. If I Enable, run email task, and disable the task again it does not send out the email...
Hope this makes someone sense...
So that is where I sit, unless I am missing something obvious I am not sure what else to do at this point.
February 13, 2007 at 8:59 am
First correct this section of code:
oStep.DisableStep = False
oStep.ExecutionStatus = DTSStepExecStat_Waiting
oStep.DisableStep = TRUE
Next, add an ActiveX task. That task will have these lines of code:
dim oStep
Set oStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_4")
oStep.DisableStep = TRUE
Next, join the executesql task that sends your email to the activex task with a workflow (succeed or completion).
Russel Loski, MCSE Business Intelligence, Data Platform
February 13, 2007 at 9:24 am
Worked like a charm...
I was inside the box and didn't see the whole picture...I guess if I would have staired at it long enough I would have realized I could do that vs. putting that code within the previous ActiveX.
Thanks again for the help and explination...taught me a lot about ActiveX that I didn't know.
Leeland
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply