Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS switchboard/menu/dashboard ... ?


SSIS switchboard/menu/dashboard ... ?

Author
Message
brrimes
brrimes
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
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
tripleAxe
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 13004
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
brrimes
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
Thanks for the post tripleAxe. What are your specific thoughts on the GUI? What tool to create that?
tripleAxe
tripleAxe
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 13004
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
brrimes
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
A screen is needed for a business user. So, you might suggest building it in VB.NET in Visual Studio?
sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2162 Visits: 487
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
brrimes
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
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
tripleAxe
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 13004
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
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2162 Visits: 487
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
brrimes
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 25
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 ... :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search