December 20, 2007 at 8:14 pm
I'm attempting to re-write some DTS packages into SSIS. The first step in our old DTS packages was to check a folder for any files. If there are any they are copied into a \source directory and them moved into an \archive directory. The files are then processed from \source. The next time the job schedule is run, if there are no files to process the job ends. No stored procedures are run (those are the job steps after the dts package).
The way that this logic was implemented in 2000 was:
The copy/move files step was ActiveX. If files were found,the dts step was set to report failure and the workflow step to load the files was "on failure". If no files were found the step would report success and the dts package would end, reporting success and the job step, when this is reported, the job would also end. (on failure the job would continue).
This clever method works, but its' biggest issue is if there are other errors in the dts package they may not be noticed (without additional error trapping).
Is there a better way to do this type of Job Workflow/ SSIS workflow in 2005?
Sorry so long a post..
Thanks in advance for any assistance!
January 18, 2008 at 7:52 am
I would suggest looking into using a Foreach Loop Container. You can tie the collection to a particular folder, and if there are no files there, the loop would be executed zero times. Package all of your iterative processing in this loop.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply