SSIS switchboard/menu/dashboard ... ?

  • brrimes

    Old Hand

    Points: 307

    I'm working on an application that will have about a dozen SSIS packages. These will be run manually on different days and times.

    What are the options for putting these onto one screen with 12 buttons (1 per package) that can be clicked to launch the package?

    Thx.

  • tripleAxe

    SSCertifiable

    Points: 5605

    One option is to have SQL Server Agent Jobs set up to run the SSIS packages, but don't schedule the jobs to run automatically, and then have a web application or other front-end app that you are building start the relevant job using the sp_start_job stored procedure in the msdb database. You will need to grant permissions to whatever security account you are using to execute this procedure. I've done this before by granting the login membership of the SQLAgentOperatorRole in msdb. You might then want to build in some sort of mechanism to poll the execution status and history of the job to display in your GUI.

  • brrimes

    Old Hand

    Points: 307

    Thanks for the post tripleAxe. What are your specific thoughts on the GUI? What tool to create that?

  • tripleAxe

    SSCertifiable

    Points: 5605

    I don't really know enough about what you are trying to do and who you are building it for. If it was an internal tool just for me I would simply leave it as a set of SQL Server Agent Jobs and run them from the SQL Server Management Studio GUI. For some tasks I've not gone that far even, and simply start up BIDS or SQL Server Data Tools and run the package manually.

    I've also worked with Dev teams that have build web-based front-ends using Visual Studio to control SSIS packages like this too.

    I guess it comes down to your particular requirements and the tools and skill-sets you have available to you.

  • brrimes

    Old Hand

    Points: 307

    A screen is needed for a business user. So, you might suggest building it in VB.NET in Visual Studio?

  • sneumersky

    SSCertifiable

    Points: 7667

    I second that suggestion.

    Are there any dependencies amongst the 12 packages (i.e. parent/child packages), and does an end user have control over passing any optional parameters? I ask because I have seen clients implement sp_update_job in order to update these SSIS parameters, which can introduce another level of complexity to a custom administrative environment depending upon who the job owner is.

  • brrimes

    Old Hand

    Points: 307

    No, there are no dependencies and no inputs from the user.

    But that does bring up another question. There is an Excel spreadsheet that is input to the process and each month, it's folder and name changes (related to the month). Is there anyway I can have SSIS package read a table with the new/current foldername and spreadsheet name and have it update the connection within the package?

  • tripleAxe

    SSCertifiable

    Points: 5605

    Yes, you could read the file name and path into a variable and then use the variables in the expressions property of the connection manager for the excel spreadsheet.

  • sneumersky

    SSCertifiable

    Points: 7667

    Many ways to accomplish this--especially if you know the folder names before hand. You can use an execute sql task to update a variable(s) which contains connection information. These variables would be used in the excel connection manager and be updated at runtime by the execute sql task.

    Be careful though. If the Excel worksheet is a source component in a data flow input and its metadata is not the same (i.e. column names or data types) as the Excel worksheet under which you did your development, the data flow component can throw errors, which you will need to catch and handle these errors.

  • brrimes

    Old Hand

    Points: 307

    It's good to know that variables can be used in connections.

    And thanks for the warning about the data types.

    These spreadsheets aren't supposed to change month to month, but when the data is coming from end users ... 🙂

  • sneumersky

    SSCertifiable

    Points: 7667

    Exactly!

  • Phil Parkin

    SSC Guru

    Points: 244753

    There are some issues that you will face with this solution.

    Let's assume that you have the packages developed & deployed. You have 12 SQL Agent jobs created. You have built a client app that connects to the relevant SQL instance and runs sp_start_job when a certain button is clicked on the form.

    Your first issue is user feedback. sp_start_job runs asynchronously and therefore you cannot easily monitor the progress of the job from the client app.

    When the job completes, how will the user know?

    If the job goes wrong, how will the user know?

    What happens if several users click the same button?

    Do you want to give your users rights to start SQL Agent jobs?

    How do you prevent the same file being processed more than once?

    (OK, the last one is easy: archive it after processing.)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 12 posts - 1 through 12 (of 12 total)

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