Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS switchboard/menu/dashboard ... ? Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:34 AM
Points: 6, Visits: 8
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.
Post #1526536
Posted Thursday, January 02, 2014 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 251, Visits: 6,062
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.
Post #1527152
Posted Thursday, January 02, 2014 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:34 AM
Points: 6, Visits: 8
Thanks for the post tripleAxe. What are your specific thoughts on the GUI? What tool to create that?
Post #1527162
Posted Thursday, January 02, 2014 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 251, Visits: 6,062
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.
Post #1527163
Posted Thursday, January 02, 2014 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:34 AM
Points: 6, Visits: 8
A screen is needed for a business user. So, you might suggest building it in VB.NET in Visual Studio?
Post #1527166
Posted Thursday, January 02, 2014 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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.
Post #1527167
Posted Thursday, January 02, 2014 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:34 AM
Points: 6, Visits: 8
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?
Post #1527171
Posted Thursday, January 02, 2014 9:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 251, Visits: 6,062
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.
Post #1527174
Posted Thursday, January 02, 2014 9:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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.
Post #1527175
Posted Thursday, January 02, 2014 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 8:34 AM
Points: 6, Visits: 8
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 ...
Post #1527239
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse