Solved

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

Posted on 2010-09-21
6
1,692 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

726 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