I wonder if anyone can help me with a question.
Context I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves the data into some tmp tables then runs a couple of stored procs to move the data into the correct tables. I'm struggling to deploy my package successfully.
Hurdles I've already jumped over
- The SSIS package is functional in debug mode.
- I've got a SQL Agent job which can run the package on the integration services server file system.
- I've managed to create a way of the users starting the job from excel and inheriting
SQLAgentOperatorRolethrough the very convoluted process of creating certificates and a new stored proc, I called it
spTriggerJob [with parameter for job name], which then runs
EXEC msdb.dbo.sp_start_job @job_name = @strJobwith the correct privileges.
- I've created and implemented a proxy with permissions to access the network file server.
- I've created XML configurations to pass the required variables (e.g. the filepath of the file to be uploaded) and a way of updating them on-the-fly.
When I run the package manually from SSMS I can load the XML configurations successfully. However I haven't been able to do this from the SQL Agent job. If I go into
job properties > steps > edit > configurations tab > add the only options I get are C:\ D:\ and a couple of obscure/unhelpful network drives. Ideally I want to point it at a full UNC filepath. Is it necessary to map the drive on the server?
I suspect there is a more optimal way of deploying my SSIS package which circumvents this. Can anyone suggest a better way? I'm fairly new to all this so don't make any assumptions as to my existing knowledge!
When I use my
spTriggerJob [name of job], I see a weird message in the job history. It says "The job succeeded. The Job was invoked by user " then it gives the Windows account login of one of the network administrators. However I've been able to verify that the permissions of the proxy (which have nothing to do with Mr Network Admin) are applied correctly by using it to create an entry in a test table and recording the user name.
However if I call msdb.dbo.sp_start_job directly, then the login which appears in the job history message is my own.
This seems wrong! Does anyone know how to resolve this?
Edit: I figured out problem 2. It is because 'spTriggerJob' has to have 'WITH EXECUTE AS OWNER'. Whilst I thought I was owner, in fact it is Mr Network Admin. The confusing bit was that the job was running with the correct privileges inherited from the proxy, which threw me off a bit.
Required solution I need to reach a point where the package is run on an ad hoc basis at the click of a button, with different configuration settings each time it is run. Surely it can't be that hard!!!
Thanks in advance for your help.