September 17, 2010 at 1:51 pm
We have flat files sent over nightly from our ERP system, which are used in our morning datamart load. They are processed via a SQL job which calls a number of DTSX and DTS packages.
Unfortunately, the times when the files are sent over to us can fluctuate, due to both files growing throughout the year (longer processing to create and transfer them) and the occasional issue with Day Close (delayed processing and transferring).
Currently, the daily SQL job to process these files launches at 6:30 AM, regardless of whether the files have been sent. We also have problems when the file is still being FTP'd over and the SQL job tries to access the file.
What can I put in place to check for exclusive access to the file and to kick off the appropriate DTSX package? So, instead of launching my whole job at 6:30 AM, I process each file individually, once it is available.
Thanks in advance.
September 17, 2010 at 2:52 pm
September 20, 2010 at 11:21 am
For those of us who can't install extra little stuff because of corporate restrictions, put everything you need to do inside a ForEach container. Then set the SSIS package running job to run every 5 or 10 or 5 minutes. If the files are in the folder, the tasks in your package will run. If they aren't, the ForEach container won't find the files and won't run the tasks, but the job itself will succeed either way.
September 23, 2010 at 4:02 pm
You might want to look at the WMI Event Watcher task in SSIS 2008. Kirk Haselden has an example of monitoring a directory for a file to be created using this task in chapter 11 of his book "Microsoft SQL Server 2008 Integration Services Unleashed".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply