Dynamic Scheduling of a SSIS Step
-
Hi All,
I need a favor, regarding the below topic:
I have an SSIS to update a table on multiple servers and there is a requirement to run the updates (daily/weekly or Monthly) configured in a table on the basis of the different country(CorpRegionID) & Vendor(VendorID).
Table structure:
Could you please suggest me the best approach to implement this dynamic scheduling a step. Although we are running the ssis in every 3 minutes.
-
Although we are running the ssis in every 3 minutes.
Are you saying that this is what you do currently and you want to change it? If so, as 'Daily' is your most frequent option, why run it every three minutes?
Are you looking for something which automatically configures the schedule of a SQL Agent job, or something else?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I do something that might be what you want.
In your table above, does RegenerateFlag tell whether that country/vendor should be run? If so, then you need a process to set that flag at the start of your SSIS package. You would also need to have a LastRunDateTime column in your table. If the current time is greater than the interval, set RegenerateFlag to 1, else to 0.
I embed this in a sequence container in SSIS for each update:
Before each update is run, use a SQL Task to query the table for the RegenerateFlag. Use an Expression and Constraint operation on the precedence constraint to move to the next
Following the update, set the LastRunDateTime column in the table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply