We have a direct file provided by customers in which we have 3 jobs set in SQL agent to filter shape and distribute the data into other tables.
I have to run them manually through step.
What methods do you use to ETL your data? Is this common having a gate keeper running manual jobs then either dropping the job id record if the loads bombs or making updates if need, through manual updates.
It doesn't seem like a great way to do things but I have never run jobs before to load data.
Any thoughts, insights, or examples would be greatly appreciated.
You can string steps along in SQLAgent. By default, the job halts when an error is reported by one of the SSIS packages (make sure that the SSIS packages error out correctly, of course. They can be rigged to "succeed" no matter what happens). Set up a job with three steps, which call the packages in order of execution. If the first package errors out, you can work on getting that fixed, then run the job starting at the second step manually. Try it out a bit using simple insert statements on a table. You will need to get to know SQLAgent's limitations as it is not a great workflow tool.