Attention, Looking for DTS input using the FSO Obj

Question

Attention, Looking for DTS input using the FSO Obj

kbrady

SSCommitted

Points: 1861
More actions
June 28, 2002 at 7:30 am

#105084

I’m in the process of creating some general error checking before running a DTS package. Here are the choices to be made prior to running the package.
If the source file exists in my source directory then I want to
run the package: Main = DTSTaskExecResult_Success
If the source file exists in my destination directory then I want to roll back the package and rerun it: DTSTaskExecResult_Success
And last if the source file does not exist in either the source directory or the Destination directory then the package will not run at all: DTSTaskExecResult_Failed
The problem that I am having is specifying in the first two scenarios: DTSTaskExecResult_Success
Which one to run according to the results of my code listed below?
In both cases my workflow is labeled “Success” thus both would run.
Here is the code that I have currently in ActiveX using FSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strDTSSourceDir) Then
Results = "File Does Exists! Package has started."
MsgBox (Results)
Main = DTSTaskExecResult_Success
Else if objFSO.FileExists(strDTSDestinationDir) Then
Results = "This package has already run for the current month. Package is now rolling back."
MsgBox (Results)
Main = DTSTaskExecResult_Success
Else
Results = "The File VIAMRS DOES NOT EXIST IN either the Source/Destination Directories, thus DTS package will not run."
MsgBox (Results)
Main = DTSTaskExecResult_Failed
End If
End If

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

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

crosspatch Hall of Fame Points: 3108 More actions · Answer 1

Not quite sure what you're trying to acheive with the 'Package Rollback'. How can the Source file be either in the Source or Destination directories, surely Source contains Source File, Destination should contain Destination File?

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 2

When you say "roll back the package and rerun it" how do you mean? You want to keep executing step 1 until the source file does not exist?

Regards,

Andy Jones

andyj93@hotmail.com

.

kbrady SSCommitted Points: 1861 More actions · Answer 3

Thanks folks I decided that I would reconfigure the individual tasks in the package to make this thing work. Thanks for your responses!!!

HOWEVER one other question – Looking for script where I can send email through the ActiveX task. Is there a way to send email through this task” Thanks

bmsjr Ten Centuries Points: 1199 More actions · Answer 4

You may have to set some things up to send mail through an activeX task. There is a KB article on setting up CDOSys on a SQL server. I can't remember the article number off the top of my head but it is out there. You could use this type of solution. I also have used BLAT to send mail from a command line and I would imagine you could do this from script. I know in order to get CDOSys to run on the SQL server though I had to add SMTP services.

Bruce Szabo, MCSE+I, MCDBA, MCSD

kbrady SSCommitted Points: 1861 More actions · Answer 5

Thanks Bruce for your input. I beleive that I had accually created another post under general asking the same question. Here is the link incase you were curious.

http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=5553&FORUM_ID=23&CAT_ID=2&Topic_Title=XP%5FSENDMAIL&Forum_Title=General