Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running SSIS package


Running SSIS package

Author
Message
larry.malley
larry.malley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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)
Attachments
jostep_adv.png (7 views, 21.00 KB)
hjackson67 52048
hjackson67 52048
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 55
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.
hjackson67 52048
hjackson67 52048
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 55
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.
larry.malley
larry.malley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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).
hjackson67 52048
hjackson67 52048
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 55
Yes, that's your next step. Then just run the job.
Please let us know how it turns out.
larry.malley
larry.malley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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?
hjackson67 52048
hjackson67 52048
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 55
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.
larry.malley
larry.malley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 25
Not finding anywhere to associate the proxy with a server role. Sad

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.
larry.malley
larry.malley
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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?
hjackson67 52048
hjackson67 52048
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 55
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search