Check txt ext exists using dts
-
Hi guys,
I have written a dts package to download a file from the ftp. the file should be ready every morning at 9. the dts will run at 10.
the think I need to do is be able to check if any file if on the location. if a file is prescent then run.
this way it will send me an email to alert me that the file has not been placed yet. the problem is i will only know the file extension i.e. .txt the file may change its name with date and times and other parts.
does anyone know how I will check if a text file is prescent in a directory?
Cheers
Paul (New to sql from mysql
) -
As long as you can build the filename - you can use an undocumented extended procedure called xp_fileexist to check if the file exists. The following link may help you with that.
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp
If however, you cannot build the full name of the file, then you can do it in the following way - which may be a bit clumsy - but it works
create table #temp (dirout text)
insert into #temp
exec ('xp_cmdshell ''dir c:\Maint\*.txt''')
select * from #temp
if exists (select 1 from #temp where dirout like '%txt%')
print 'At least one Text file was found'
else
print 'No Text files found'
-
If the file is the only one in the directory, you could also use an ActiveX script as the first step in your DTS.
The script would be something like:
Function Main()
Dim oFSO, oFld
Dim sFolder
Dim nFiles
sFolder = "c:\maint"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFld = oFSO.GetFolder(sFolder)
nFiles = oFld.Files.Count
' Check for file and return appropriate result
If nFiles > 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
Set oFSO = Nothing
Set oFld = Nothing
End Function
You could then have an "on failure" workflow going to a sendmail task and an "on success" workflow on the rest of your package.
Hope this helps, let us know how you got on.
Adrian
-
Many thanks. Think I will have a bash at the activex route. If I can grab a filename then store it as a global, problem solved. any more ideas guys?
thanks again for your help so far for this VERY VERY newbie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply