June 6, 2003 at 6:58 am
I want to create a DTS package that creates calls in our CRM database based on information within a file that I get from another application. My first check within the package is to check and see if this file exists within a particular folder. Obviously this would be the first step in my package and the package would move to step 2 if the file does exist. If not, it would stop execution. What would be the best way to check whether a file exists or not within a certain folder on our network within a DTS package?
Regards,
Bessebo
June 6, 2003 at 7:18 am
I prefer to use a vbscript task to determine if a file exists !
Try this :
Option Explicit
Function Main()
Dim objFSO
set objFSO = createObject("Scripting.FileSystemObject")
If objFSO.FileExists("O:\MyFolder\MySubFolder\File.txt") Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
Exit Function
ErrorExit:
Main = DTSTaskExecResult_Failure
End Function
Andy P
Andy P
June 6, 2003 at 7:22 am
Andy,
That is awesome. Thanks for the help.
Regards,
Bessebo
June 6, 2003 at 8:48 am
Andy,
I just incorporated that code into a package and it worked like a charm. I now want to be able to rename the file to I was checking the existence of to a different name or to a different folder. I have been looking into the vbscript references for how to do this and cannot find it yet. Any ideas?
Thanks,
Bessebo
June 6, 2003 at 9:20 am
I also do this I use the commands (in the relevant place) :
objFSO.CopyFile "src-Path\filename", destination-Path\filename",true
[where last boolean is for overwrite option]
and use the same folder
and then
objFSO.DeleteFile "Path\FileName"
to delete the original.
Hope this helps.
Andy P
quote:
Andy,I just incorporated that code into a package and it worked like a charm. I now want to be able to rename the file to I was checking the existence of to a different name or to a different folder. I have been looking into the vbscript references for how to do this and cannot find it yet. Any ideas?
Thanks,
Bessebo
Andy P
Andy P
June 6, 2003 at 9:33 am
Andy,
Thanks. Do you have a copy of an Acgive Script Task where you perform this logic? If so could you forward it along or email me directly at bbessette@sensitech.com.
Regards,
Bessebo
June 9, 2003 at 1:18 am
I'll see what I can dig up....I know that most of my scripts that use the FSO are broken in 2 and exist either side of sql tasks. Plus they use the global variables for passing data (filenames etc) between them. (I will have to remove all paths etc but you should be able to follow the flow). I'll try and get around to it this morning.
Andy P
Andy P
June 10, 2003 at 8:13 am
Appologies for delay.......exceptionally busy at the moment.......
I cannot find (quickly) a rename example.
The following example uses the global-variables to store the working names.
NOTE : The scripts are separate vb tasks within a dts. global variables are used to pass values accross the tasks.
I use the move to rename when transient !
Hope this helps.
Regards
Andy P
'***********************************************************************************************************************
'1.Determine if there is anything to do and set global dts variables
'***********************************************************************************************************************
Option Explicit
Function Main()
Dim objFSO
Dim objFolder
Dim ThisFile
Dim objFile
Dim strWorkingFolder
Dim strWorkingFileName
dim strArchiveFileName
Dim intTotalXMLFiles
'Get working folder location
strWorkingFolder = DTSGlobalVariables("WorkingFolder").value
'Get working filename
strWorkingFileName = DTSGlobalVariables("WorkingFile").value
'Initialise local variables
intTotalXMLFiles = 0
'See if there are any files to process If there are then get the name of a file to process and store the count of files that need to be processed
set objFSO = createObject("Scripting.FileSystemObject")
set objFolder = objFSO.GetFolder(strWorkingFolder)
For Each ThisFile In objFolder.Files
If LCase(Right(ThisFile.Name, 3)) = "xml" Then
Set objFile = ThisFile
intTotalXMLFiles = intTotalXMLFiles + 1
strArchiveFileName = ThisFile.Name
End If
Next
'If we have anything to do then store the name and rename to the working filename
If intTotalXMLFiles > 0 Then
DTSGlobalVariables("TempArchiveFilename").value = objFile.Name
objFile.Name = strWorkingFileName
Else
End If
'Always set global counter so zero files can be catered for
DTSGlobalVariables("FilesToProcessCount").value = intTotalXMLFiles
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
Main = DTSTaskExecResult_Success
Exit Function
ErrorExit:
Main = DTSTaskExecResult_Failure
End Function
'***********************************************************************************************************************
'4a.(If 3 fails) Restore the filename and move into the error folder
'***********************************************************************************************************************
Option Explicit
Function Main()
Dim objFSO
'Rename back to orignal name and archive the working file
set objFSO = createObject("Scripting.FileSystemObject")
objFSO.MoveFile DTSGlobalVariables("WorkingFolder").value & DTSGlobalVariables("WorkingFile").value, DTSGlobalVariables("ErrorFolder").value & DTSGlobalVariables("TempArchiveFilename").value
Set objFSO = Nothing
Main = DTSTaskExecResult_Success
Exit Function
ErrorExit:
Main = DTSTaskExecResult_Failure
End Function
Andy P
Andy P
June 10, 2003 at 9:16 am
Thanks Andy for the help..
Regards,
Bessebo
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply