Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running SSIS package Expand / Collapse
Author
Message
Posted Thursday, June 12, 2014 9:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:33 PM
Points: 5, Visits: 25
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)


  Post Attachments 
jostep_adv.png (6 views, 21.49 KB)
Post #1580128
Posted Saturday, June 14, 2014 7:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:30 PM
Points: 12, Visits: 35
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.
Post #1580906
Posted Saturday, June 14, 2014 7:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:30 PM
Points: 12, Visits: 35
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.
Post #1580907
Posted Tuesday, June 17, 2014 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:33 PM
Points: 5, Visits: 25
@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).
Post #1582433
Posted Tuesday, June 17, 2014 2:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:30 PM
Points: 12, Visits: 35
Yes, that's your next step. Then just run the job.
Please let us know how it turns out.
Post #1582583
Posted Tuesday, June 17, 2014 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:33 PM
Points: 5, Visits: 25
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?
Post #1582588
Posted Tuesday, June 17, 2014 11:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:30 PM
Points: 12, Visits: 35
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.
Post #1582685
Posted Wednesday, June 18, 2014 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:33 PM
Points: 5, Visits: 25
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.
Post #1583360
Posted Wednesday, June 18, 2014 6:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 2:33 PM
Points: 5, Visits: 25
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?

Post #1583496
Posted Wednesday, June 18, 2014 11:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:30 PM
Points: 12, Visits: 35
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.
Post #1583542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse