January 3, 2019 at 7:56 am
I have a SSIS package that runs from a stored proc. Let'' call this stored proc "RunMySSISPackage".
This stored proc basically calls these [SSISDB].[catalog] procedures in this sequence. (I think most SSIS experts will know what I am talking about.)
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution]
I want to trigger RunMySSISPackage proc from a button on a Webform.
I want to use a dedicated SQLServer Login to execute the stored proc. Otherwise, if I use Integrated Security, I would have to give appropriate access to everyone who needs to press the button. So to avoid that, I want to use a dedicated SQLServer Login. But the trouble with this is that the SQLServer Login does not have access to the network folders where some of the SSIS operations need to happen.
Can someone point me in the right direction on how I can go about doing this?
January 3, 2019 at 9:08 am
I was forced into research mode when I implemented this functionality as customization to a commercial asp.net application. If you’re doing straight asp.net, it should be a bit simpler. What I found was that the report execution service was central to calling SSRS from the web app. The service I called was ReportExecution2005.asmx. “2005” doesn’t refer to a SQL Server version, as I used it with SSRS on 2008 R2, though there may be a newer version depending on your SQL Server version.
I also used a dedicated SQL Server login, and set connection credentials in code. Access to folders on disk is provided by the ReportExecutionService Class, which is part of the ReportExecution2005.dll. It accepts a System.Net.NetworkCredential credential, which is created using an AD account that has access to disk.
You should find several examples if you google
ReportExecution2005.asmx asp.net
January 3, 2019 at 1:26 pm
DoolinDalton - Thursday, January 3, 2019 7:56 AMI have a SSIS package that runs from a stored proc. Let'' call this stored proc "RunMySSISPackage".This stored proc basically calls these [SSISDB].[catalog] procedures in this sequence. (I think most SSIS experts will know what I am talking about.)
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution]I want to trigger RunMySSISPackage proc from a button on a Webform.
I want to use a dedicated SQLServer Login to execute the stored proc. Otherwise, if I use Integrated Security, I would have to give appropriate access to everyone who needs to press the button. So to avoid that, I want to use a dedicated SQLServer Login. But the trouble with this is that the SQLServer Login does not have access to the network folders where some of the SSIS operations need to happen.
Can someone point me in the right direction on how I can go about doing this?
I am not sure I understand why you would create a process in SSIS that is executed by a user. What would happen if more than one user launched this process at the same time? Generally - SSIS would be scheduled to run at a set time instead of on-demand.
What is the workflow that requires the user to start the SSIS process?
If you really want this controlled by a stored procedure - you need to create that procedure with EXECUTE AS and set it up to execute as the login that has the appropriate permissions. The easiest method would be to create a SQL Server Agent job - with the job step set to run using a proxy account. Your code will then just start the agent job and that can be accomplished using a SQL login with appropriate permissions.
What I would do is just schedule an agent job to run every xx minutes - where the package checks conditions and processes based on those conditions. For example, if this is to process a file then SSIS looks to a specific folder for the file and if it exists - processes the file. If the process needs different parameters based on something the users enter - then that data would be entered into a table and the SSIS package reads the table and processes based on the values in that table, marking the table entry as completed when completed (or delete the row when completed).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2019 at 1:39 pm
I'm an idiot. This is an SSIS forum, right? I saw your question and thought SSRS.....not sure why.
Sorry for the misdirection.
January 3, 2019 at 3:06 pm
palandri - Thursday, January 3, 2019 1:39 PMI'm an idiot. This is an SSIS forum, right? I saw your question and thought SSRS.....not sure why.Sorry for the misdirection.
Yes, I was bit thrown off... My question was for SSIS
January 3, 2019 at 3:27 pm
Jeffrey Williams 3188 - Thursday, January 3, 2019 1:26 PMDoolinDalton - Thursday, January 3, 2019 7:56 AMI have a SSIS package that runs from a stored proc. Let'' call this stored proc "RunMySSISPackage".This stored proc basically calls these [SSISDB].[catalog] procedures in this sequence. (I think most SSIS experts will know what I am talking about.)
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution]I want to trigger RunMySSISPackage proc from a button on a Webform.
I want to use a dedicated SQLServer Login to execute the stored proc. Otherwise, if I use Integrated Security, I would have to give appropriate access to everyone who needs to press the button. So to avoid that, I want to use a dedicated SQLServer Login. But the trouble with this is that the SQLServer Login does not have access to the network folders where some of the SSIS operations need to happen.
Can someone point me in the right direction on how I can go about doing this?
I am not sure I understand why you would create a process in SSIS that is executed by a user. What would happen if more than one user launched this process at the same time? Generally - SSIS would be scheduled to run at a set time instead of on-demand.
What is the workflow that requires the user to start the SSIS process?
If you really want this controlled by a stored procedure - you need to create that procedure with EXECUTE AS and set it up to execute as the login that has the appropriate permissions. The easiest method would be to create a SQL Server Agent job - with the job step set to run using a proxy account. Your code will then just start the agent job and that can be accomplished using a SQL login with appropriate permissions.
What I would do is just schedule an agent job to run every xx minutes - where the package checks conditions and processes based on those conditions. For example, if this is to process a file then SSIS looks to a specific folder for the file and if it exists - processes the file. If the process needs different parameters based on something the users enter - then that data would be entered into a table and the SSIS package reads the table and processes based on the values in that table, marking the table entry as completed when completed (or delete the row when completed).
Why run SSIS by a user? Well... we have a job that needs to be run on demand and it takes a user specified date parameter. Basically, the package takes the date, and creates some file based on that date and then does some other ETL processes using that file.
But basically, for the sake of this question, we are running a stored proc through a button on a Webform. And the stored proc happens to execute an SSIS package. But the issue is, the SSIS package needs to do some file operations and that's where running the package using a dedicated SQL Server Login gets me stuck because it doesn't have file system permissions.
So then... you suggested few solutions.
(1) Use EXECUTE AS. We tried this. Inside the stored proc, we have EXECUTE AS [Some Windows Login]. This login has enough file permissions to do all the things that the package wants to do. So that works. But what I don't like about it, the SQL Server Login that calls the stored proc seems to need a sysadmin server permission to be able to use the EXECUTE AS. And the password for this sysadmin SQL Server Login is visible in the Webforms application.
(2) You also suggested creating a SQL Agent job and have the button run the job. (There is that sp_blahblah command I can't think of off the top of my head...) But this doesn't work because how would I pass in the user specified parameter?
January 4, 2019 at 5:34 am
Consider implementing a simple queuing system. When a user 'clicks the button', details get written to a table (eg, username, createdate, parameters...)
When the SQL Agent job runs, it checks the queue, runs operations for any new items, then marks completed items as done (or archives them elsewhere).
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 4, 2019 at 7:35 am
Phil Parkin - Friday, January 4, 2019 5:34 AMConsider implementing a simple queuing system. When a user 'clicks the button', details get written to a table (eg, username, createdate, parameters...)When the SQL Agent job runs, it checks the queue, runs operations for any new items, then marks completed items as done (or archives them elsewhere).
That thought did cross my mind. Have an agent job scheduled to run every minute or so. It will check the queue table and only execute the proc if there is a signal in the queue that tells it to execute.
But really, there is no way to just have button press execute an SSIS proc without into folder permission issues?
February 8, 2019 at 1:59 pm
DoolinDalton - Friday, January 4, 2019 7:35 AMPhil Parkin - Friday, January 4, 2019 5:34 AMConsider implementing a simple queuing system. When a user 'clicks the button', details get written to a table (eg, username, createdate, parameters...)When the SQL Agent job runs, it checks the queue, runs operations for any new items, then marks completed items as done (or archives them elsewhere).
That thought did cross my mind. Have an agent job scheduled to run every minute or so. It will check the queue table and only execute the proc if there is a signal in the queue that tells it to execute.
But really, there is no way to just have button press execute an SSIS proc without into folder permission issues?
You can probably do this with the proxy feature in SQL Server - essentially it allows you to grant a user - in your case, an SQL Server account - the ability to use the permissions of another user (such as a service account with access to the folder), without exposing the password of the proxied account.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply