June 12, 2014 at 9:42 am
Having trouble configuring the SQL Server Integration Services and SQL Server Agent to run a SSIS package. The package copies files from a network share.
I imported the package with a storage protection level. I set up an SSIS Package Execution proxy with network rights, but can't find the "run as" for the job step. Shouldn't it be in the Job Step Propoerties, Advanced dialog (see attached)?
version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
June 14, 2014 at 7:12 pm
Larry-- I had a similar problem a couple of weeks ago when I was setting up a new developer on a test server. Let's assume for the moment that the Proxy Account has been set up correctly (credentials, etc.). If there are no problems with the Proxy then the user that is creating the job must have the right to create a SQL Agent job. So, what role does the job creator account have in MSDB? What role has been assigned to the proxy account in MSDB? Does the proxy account have access to all of the databases that are accessed in the job? Finally, your attachment does not show the set-up page that has the Run-As dropdown. Did you associate the Proxy account with the SSIS subsystem so that it will have the ability to execute SSIS packages? I know that there are a lot of questions here but you can use them as a checklist to make sure that you've covered all of your bases.
June 14, 2014 at 7:18 pm
Almost forgot - if you have not yet had a chance to do so, you should read the excellent Stairway to SQL Sever Agent by Richard Waymire if you're going to be creating many SQL Agent jobs in the future. Just a suggestion.
June 17, 2014 at 10:08 am
@hjackson57
I verified the login used by the proxy has read/write access to the database, as well as read privileges on the network share. It is set up under SSIS Package Execution. My thinking was that I have to actually specify the proxy name in the job step, in the advanced properties page (attached).
June 17, 2014 at 2:00 pm
Yes, that's your next step. Then just run the job.
Please let us know how it turns out.
June 17, 2014 at 2:16 pm
I'm unable to find where proxy run-as is configured in Job or job step. Should it just automatically use a proxy in the SSIS subsystem?
June 17, 2014 at 11:52 pm
Proxies are part of SQL Agent. Did you create a Proxy for SSIS? If so, and you did not have the option to select the Proxy, you've got some permissions missing. You will need to grant the Proxy permission to run jobs. The easiest way to do that is assign the Proxy the ssisadmin role. After that you should be good to go (if you've done you set up in msdb). There you should have granted at least sqlagent operator role to Proxy.
June 18, 2014 at 1:17 pm
Not finding anywhere to associate the proxy with a server role. 🙁
Actually there's something in the SSIS package context menu that says Package Roles... where I see my package has ssisadmin roles for both read and write.
June 18, 2014 at 6:43 pm
Good news, I found the run as dropdown on the General page of the Job Step properties dialog, not the Advanced page like I saw in a screen shot online, but SQL Server Agent still can't run the job to read from a network share (with a UNC path).
Starting to be concerned because the user account with the highest permissions I have access to does not have change or delete permissions on the network share. I have demonstrated to myself that SQL Server Agent will run the job when the SSIS package is pointed to a local directory to read files. Would lack of delete or change permissions somehow prevent SQL Server Agent from reading?
June 18, 2014 at 11:37 pm
UNC paths can be tricky sometimes. Since you are using that, has it been set-up as a fileshare? The proxy account will need read write access to the share. If you have admin rights on the server that you are writing to (the fileshare) then you can set the share up yourself.
After you've verified that you can read and write to the share, try something simple like creating a package with a File System Task and see if you can move a file to the share in a SQL Agent job using the proxy to execute the job. If you can then you are in business.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply