August 7, 2015 at 8:29 am
Hi Everyone,
I want to complete this task but don't know how. Need help!!
I want to give Users to execute SQL AGENT JOB. SQL Agent is running SSIS Package. I was thinking to give some kind of Click Button Access to user so user can execute the SSIS Package/SQL Agent Job,The folder I will create in Network drive and inside the folder could be Excel File to run Macro to call SSIS Package or other option. I would like to know how I can accomplish this task. FYI in user machine SQL SERVER/BIDS are not installed and Users doesn't have permission to sql server.
Please advise.
Thank You.
August 7, 2015 at 8:57 am
tooba111 (8/7/2015)
Hi Everyone,I want to complete this task but don't know how. Need help!!
I want to give Users to execute SQL AGENT JOB. SQL Agent is running SSIS Package. I was thinking to give some kind of Click Button Access to user so user can execute the SSIS Package/SQL Agent Job,The folder I will create in Network drive and inside the folder could be Excel File to run Macro to call SSIS Package or other option. I would like to know how I can accomplish this task. FYI in user machine SQL SERVER/BIDS are not installed and Users doesn't have permission to sql server.
Please advise.
Thank You.
They would need permissions in SQL Server that would allow them to run a job, and I'm pretty sure that's not a good idea, because I'm also pretty sure you wouldn't be able to stop them from running ANY given job that might already exist. That's a really bad idea. I'd start looking a lot closer at what the overall objective is, as well as what these packages do. If they are just exporting data into a spreadsheet, why not give them read-only access to the appropriate database and tables and set up Data Connections in Excel, and they can refresh the data into Excel at their convenience.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 7, 2015 at 9:04 am
Steve,
Thank You for your reply. The purpose for this task is the user can run SSIS Package without any problem or ask IT. SSIS Package is reading the data from flat file source and uses some transformations and insert the data into SQL Server.They just want to run this JOB when they receive files. Is there a way I can hard code my credential to execute that SP/Agent Job in VB Script or batch file ?
Thank You.
August 7, 2015 at 9:12 am
tooba111 (8/7/2015)
Steve,Thank You for your reply. The purpose for this task is the user can run SSIS Package without any problem or ask IT. SSIS Package is reading the data from flat file source and uses some transformations and insert the data into SQL Server.They just want to run this JOB when they receive files. Is there a way I can hard code my credential to execute that SP/Agent Job in VB Script or batch file ?
Thank You.
If the primary objective is for them to get the data into a table in SQL Server, why not set up a file watcher? The concept is that you set up the file watcher to look in a given folder at a given time (or times) of day, and let the file watcher kick off the SSIS package. All the users then have to do is place the flat file in the appropriate network share. By virtue of the file watcher running on the server, user permissions are no longer an issue. The file watcher could be a VBScript, and running on the server, it could run 24 x 7 x 365, or at predetermined scheduled times. Whenever it finishes running a package, it just starts over. For server re-boots, you just terminate the task after shutting down SQL Server, and you can set up the script to run automatically on startup.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply