Solved

SSIS package does not run as a scheduled job from SQL Server

Posted on 2010-09-21
6
1,654 Views
Last Modified: 2013-11-30
Hello experts

I created an SSIS package on my IS Server logged in as Server/Administrator.

I have a proxy account set up with Server/Administrator credentials that is used to run the package. Server/Administrator account has sysadmin rights on the Database and is what the job is run under. I am able to run the package fine either directly from BIDS, or right-clicking the job and selecting "Start Job..." from SSMS. But if I schedule it, it gives me the following error -
Failed to decrypt protected XML node "DTS:Property" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

which seems to me as an access issue.

I have tried 2 approaches to scehdule this package to run every night as a job.
1. Upload package on IS and schedule that.
2. Run package from the filesystem.

Both don't work when scheduled, but run fine when i righ-click and run directly.

What am I missing here?
0
Comment
Question by:ms_smuc
6 Comments
 
LVL 1

Expert Comment

by:jlhuston
ID: 33725081
Sounds like the service account under which SQL Server Agent is running is the one that doesn't have the rights to the package.

You can try to set the SQL Agent service account to the account used to create the package as a *test* only.  It is not recommended to leave a service running as an administrator account.  But you can do this to verify that it is an account access issue from the SQL Agent to the package files and crypto keys.

Once verified, you'll need to make sure that the service account is the one that has access to the package.  You can export the SSIS package, log in as the SQL Agent account and then recreate the SSIS package and verify it runs - then schedule *that* package as the one to be run on a regular basis.
0
 

Author Comment

by:ms_smuc
ID: 33725253
jlhuston:

Thanks for your reply.

I have set up an "SSIS Package Execution" proxy account that uses the Server/Administration account credential. This proxy account is used to run the job. I had tried to run the job step under SQL Agent Service, but that failed even if I ran the job directly. Searching online, I read on a lot of forums that such a proxy account needs to be created to be able to run an SSIS package. Is that the right way to go?

For testing purposes, how can I set the SQL Agent Service to server\administrator as u suggest?
0
 
LVL 2

Expert Comment

by:marykdba
ID: 33726681
This is a possibility... sometimes the error message doesn't point to the exact problem.

Is your SSIS package transferring data from or to files on another computer?  This *could* be a network access issue.  The service that runs SSIS has to be an Administrator on the local machine (the one where the SQL Server is installed) - and a Domain User.  The Domain User must have proper access to the path where you are trying to access the file(s), but does not need to be a Domain Administrator.  This is the most common problem I have run across with running SSIS packages as jobs.  
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Accepted Solution

by:
bhess1 earned 200 total points
ID: 33727514
Are you using a package saved to disk?  If so, you are being bitten by the fact that the package is secured, by default with your user credentials.  To run the package in this case, you would have to have your user credentials presented to the package at run time.

There are a number of different options for securing the packages, but, if it is saved to disk, then *some form* of security must be used.  To run a package without using any security beyond that needed to access SQL Server, you need to store the package into SQL Server with the "Rely on server...." security level.  Note that, by default, even if you save the package to SQL Server, the security level is "user credentials".

Trust me - this bit me when I was starting with SSIS as well.
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33732028
maybe problem is in deployment, look at link below which has a tutorial on how to deploy the ssis package and run it as a job:
http://technotes.towardsjob.com/sql-server/steps-to-create-and-deploy-ssis-package-as-a-sqlagent-job/


0
 

Author Closing Comment

by:ms_smuc
ID: 33732867
Thanks all for your replies.

bhess1 : you are my star! I saved the package with "Rely on server.." setting and it worked like a charm. Thank you.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now