April 6, 2010 at 5:49 pm
Currently our SQL process is scheduled to pickup files in their respective directories at a scheduled time.
Majority of our job failures are due to missing files. It's a nightmare during the holidays. (who is sending files and who's not) I would like to come up with a better process which will look for the files and not have to schedule jobs on the SQL side. If the files are not found, send an email to the group responsible for the files and not have the job kick off until the file is present. When the file is dropped in the directory, call the SQL job to run. (completely automated) Has anyone gone through this exercise with their company? The three options below is what appears to be a possible solution, anything other recommendations or advice is greatly appreciated.
Looping over files with the Foreach Loop File Enumerator
File Watcher Task http://www.sqlis.com/23.aspx
Service Broker Messaging
April 6, 2010 at 7:54 pm
I have a loop which performs a file check, if the file does not exist it goes back to sleep for 10 minutes and then tries again. Once the file actually exists then the loop completes and continues on it's way.
This right now is a SQL 2000 thing so I've been able to use xp_fileexist to check for the file. This is going to be moving up to SSIS in a couple of months, I would expect that a script task would be able to do this in that situation.
April 6, 2010 at 7:59 pm
Thanks Nicholas! Can you post your code?
April 7, 2010 at 4:26 pm
Here's some activex that we use to check for a file and depending upon whether or not it exists you choose a different workflow, this can be used for looping
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim fso
Dim fold
Dim pkg
' First thing we need to do is to check if our directories are valid.
SET pkg = DTSGlobalVariables.Parent
'We use the FileSystemObject to do our
'Folder manipulation
set fso = CREATEOBJECT("Scripting.FileSystemObject")
'Here we check to make sure the Source folder for the files exists
if fso.FileExists(DTSGlobalVariables("gv_fullfilename").Value) <> "True" then
Pkg.Steps("DTSStep_DTSExecuteSQLTask_10").DisableStep = False
Pkg.Steps("DTSStep_DTSExecuteSQLTask_6").DisableStep = True
DTSGlobalVariables("gv_msg").Value = "File Not Found "
DTSGlobalVariables("gv_error_code").Value = -1
else
Pkg.Steps("DTSStep_DTSExecuteSQLTask_10").DisableStep = True
Pkg.Steps("DTSStep_DTSExecuteSQLTask_6").DisableStep = False
DTSGlobalVariables("gv_msg").Value = " "
DTSGlobalVariables("gv_error_code").Value = 0
end if
Main = DTSTaskExecResult_Success
End Function
Check out http://www.sqldts.com as well
April 7, 2010 at 6:25 pm
Thank you sir!
April 7, 2010 at 6:42 pm
I would go for the SSIS fileWatcher idea unless the volume of files is too high. That said - Nicholas' code looks like it would be just fine as well.
We ended up building something external to do the file-watching (a windows service to watch), with a custom queuing system. This was necessary only because we could be getting several hundred files being "bursted" to us within a few seconds, so trying to fire that many instances of a process that fast would tear up the server's memory. Of course that thing is handling 30K files a night on a busy night, so it's rather extreme.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2010 at 7:52 pm
Ever play around with the file watcher Matt?
April 8, 2010 at 10:00 am
SQLSeTTeR (4/7/2010)
Ever play around with the file watcher Matt?
Yes - it's reasonably straightforward. You still end up with a design decision: build it assuming the file won't be there (so you're firing the SSIS task in advance of the files arriving), or build it with a split path (look for the file, and it's not yet there, go the file watcher path.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 9, 2010 at 8:17 am
Thanks again. I am going to get started today. I'll let you know how I make out. Thanks for the direction.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply