Post reply

Run SSIS packages unscheduled with interactive prompting

  • March 9, 2019 at 5:04 pm

    #371107

    Hi,

    I hope this is the right forum.  Also, I have Googled extensively (a few days) before posting, more for the best approach rather than code.

    I am only looking for architectural advice - the best approach ("best" is relative isn't it?) - rather than code.  Although if you do have relevant code you can share then cool 🙂

    I'm rewriting ETL processing that we currently run in SAS to use SSIS instead.  The current processing allows the end user to set a parameter at run time.  This processing will be unscheduled, run on an on-demand basis.  The parameter is an ID number - an identity column - stored in a SQL Server table with relatively small rows (~200).  Usually, but not always, they would choose the last (most recent) ID.  They need to pick the correct ID so that data is filtered by the create date period.

    There are countless ways I could approach this.  Some I've researched or considered:

    1. Store the ID in a flat file
    2. Store the ID in Excel (I could even launch the ETL via Excel as a very crude UI using VBA)
    3. Manually set the ID in a SQL Server parameters table (via SSMS)
    4. Write an easier method to do #3 - Powershell with input, cmd file, etc.
    5. Create an unscheduled SQL Server Agent job (but I don't think there is a way to pass parameters to a SAJ?)
    6. Create a simple C# Winform with SQL Server integration to: A) display a UI (DataGrid of that above table), B) capture choice, C) write result to SQL Server parameters table, D) launch a "wrapper SSIS package", E) that wrapper SSIS package launches the other packages in the desired manner - parallel, sequential, correct order, error trapping, etc.

    Right now I lean toward #6, as I think it will give the best user experience, plus it's more fun 😉 - I'm a C# novice but there are tons of examples online.  This approach is more work (? depending on your input) and at the expense of future maintainability.

    Your thoughts as to the best architectural approach most welcome.

    Regards,
    Scott

    P.S.:  Useful link:  https://docs.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-ssms?view=sql-server-2017

  • March 10, 2019 at 12:09 am

    #2024029

    I'd consider invoking the package using a sql server broker queue in conjunction with a trigger on a table (some effort required) or more tactically, just a sql job polls a status table and runs the package if the conditions are correct.

  • March 10, 2019 at 3:06 am

    #2024032

    have a look at https://github.com/aleonard763/SSISFrameworkCommunityEdition
    It kind of contains most of the details you need to implement your desired functionality - even a master package to control all others.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply