December 30, 2013 at 9:24 am
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.
January 2, 2014 at 8:35 am
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.
January 2, 2014 at 8:43 am
Thanks for the post tripleAxe. What are your specific thoughts on the GUI? What tool to create that?
January 2, 2014 at 8:49 am
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.
January 2, 2014 at 8:52 am
A screen is needed for a business user. So, you might suggest building it in VB.NET in Visual Studio?
January 2, 2014 at 8:53 am
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.
January 2, 2014 at 8:59 am
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?
January 2, 2014 at 9:05 am
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.
January 2, 2014 at 9:08 am
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.
January 2, 2014 at 11:44 am
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 ... 🙂
January 2, 2014 at 12:05 pm
Exactly!
January 5, 2014 at 8:32 am
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.)
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply