Post reply

Dynamic Scheduling of a SSIS Step

  • April 29, 2020 at 8:27 am

    #3747234

    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:

    Capture

     

    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.

  • April 29, 2020 at 1:04 pm

    #3747299

    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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 5, 2020 at 12:46 pm

    #3748921

    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