• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Unable to run task in SSIS

This is a follow-up to the following thread:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23152990.html

I have created a credential and proxy. I then created a job with the one step being to run a specific SSIS task. On this step, I assigned the proxy I created as who the task should run as. I ensured that the account that was used has full control over the destination folder that the task will be accessing. I also gave the account the sysadmin role in the SQL Server database. The task fails when I tell it to run. The task failure refers me to the History log which contains nothing that helps me determine why it fails.

The task runs fine through BIDS. When I try and run it manually from the SQL Server using dtexec it fails with a "unable to access folder" error at the first file system task. This is why I am trying to run it using the SQL Server agent.
0
lapoliko
Asked:
lapoliko
  • 2
1 Solution
 
lapolikoAuthor Commented:
I found that I CAN  manually run the task by selecting the task at the File System store and then selecting run. (I.E. using dtexec). It still will not run as a job.
0
 
lapolikoAuthor Commented:
I found the solution:

The standard security on an SSIS package is "EncryptSensistiveWithUserKey" which only allows the user that created it to be able to run in as long as there is sensitive information (passwords) stored within the package.

A workaround that I am using is that I changed the package security to "EncryptAllWithPassword" which protects the entire package with a password. When you set the package as a job step, go into the properties of that job step, click on the "Command Line" tab and you should be prompted for the password. After you enter the password, the /DECRYPT switch should show up in the command line and the job should then be able to run under a different user.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now