asked
How can a user with READ only Execute an SSIS package that has a WRITE dataflow task?
Hi, Using Visual Studio 2010 and Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1). I have created an SSIS package that runs loads data from Excel to 3 tables in a database. I have write permissions on this database. I am handing this package over to other users. The package is located on a shared space and can be executed by someone with similar permissions to me. However, many users have only read access to this database. Is there a way they can also execute the package? Many thanks, Quentin
You could put it into a SQL Agent job and just schedule the job to execute so often. Would that be a possibility? This way they wouldn't have the ability to edit the package either and would be more controlled. You would need to ensure the SQL Agent service has permissions to wherever the excel files are located.
@JohnM - Agent jobs have to go via our DBA team and i am uncertain they would add it. I guess it could kick off each morning and have a wmi event 'listening' for a file to be dropped in the folder. Ideally, i want to avoid any DBA team dependencies, so if there are any other possibilities i would be keen to hear of them? Many thanks, Quentin
There is a file watcher task that might be useful. I don''t know of any way to allow the users to have the ability to write to the tables without DBA involvement. When they execute the package it will run under their credentials. Curious, are they opening the package and then executing it? If so, they have the ability to change the package to do other things which won't be very secure either. You could look at implementing a procedure that loads the data for you. SSIS would consume the data and feed the data into a procedure which you could grant execute permissions to the users and/or a domain group. However, you'd need a DBA to help set that up. File Watcher:
http://www.sqlis.com/post/file-watcher-task.aspx. My preference from what I know thus far, would be to put into an agent job and schedule it to run. Probably the most secure as far as I'm aware.
@JohnM - I haven't deployed yet to a user with less privileges. Only to someone with similar. And they opened the package and ran it. I envisioned something similar for the user with less privileges. When you say SSIS would consume the data and feed the data into a procedure...do you have an example of what you mean? I have used SSIS to load to staging and executed stored procs against those before. Regards, Q
Essentially you would consume the Excel columns into variables and then pass them into a procedure that would insert the data. Of course, if the excel worksheet is large in terms of columns, that might not be useful. Here's an example:
http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
18 People are following this question.
