SSIS Deployment - SQL Agent Failure - Configuration Issue?

Question

Good morning:

I've worked with SSIS exhaustively in the past and this one is stumping me. I almost am leaning towards a configuration issue on the SQL server versus a deployment issue but wanted to run it by you first.

I have a SSIS package - created in BIDS 2008. SQL Engine and SSIS is 2008R2. The job has two connections - OLEDB connection to SQL Server and File connection to a folder. SSIS and the SQL Engine were installed by someone else and SQL Engine runs under Network Service and SSIS runs under Local System.

I created the package on a computer on a different domain but changed the owner value once I loaded it on the SQL server and deployed to the SSIS package store with DecryptSensitiveWithPassword, providing a password. I also tried deleting the package and job from SSIS and importing through the SSIS menu in SSMS, choosing DecryptSensitiveWithPassword. Although I don't believe I would need to given my connections, I have tried running as 32-bit (in addition to 64-bit) and I've tried setting up a proxy account. I've tried using other encryption methods. I've also tried using cmdExec and running DTEXEC from the command line. The SQL data source in the package uses a SQL account, not Windows auth. For testing I opened the EVERYONE group up to write to the local file folder where the files need to be read / written.

NOTHING WORKS. The package runs fine from BIDS on the same machine and it runs fine from the SSIS server (via SSMS) and through the command line. SQL Agent is the only thing not playing nice.

The latest error (cmdExec running DTEXEC):

dtexec /file "D:\DTS\FileTransfer.dtsx" /DECRYPT mypassword

Code: 0xC0010018     Source:       Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts"> ...

I've read and reviewed several other discussions including this here (http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e13c137c-1535-4475-8c2f-c7e6e7d125fc)

BIG THANKS!

Answer 1

You mentioned the SSIS and SQL services were using local accounts, but what about the SQL Agent service?  The account running the SQL Agent service would determine the execution context of SSIS package when running through a SQL Agent job (unless a proxy account is specified for the job step).  I'm assuming running on your local machine, as well as BIDS on the server, the package execution would be under the context of your account.

I would strip out the "encrypt sensitive with password", just don't save sensitive information...and provide connection strings directly until you have the problem figured out.

Answer 2

Hi there,

It could be permissions issue.

trouble shoot in the following ways:

1) Use the same service account that is using by the sql server.

2) where is your SSIS package located. Is it file based. If so make sure your account have full control on that folder.

Still have error post a screen shot or error message.

thanks

kumar

Answer 3

SQL Agent is using Network Service.

I actually moved to make it file based, remove all decryption (dontsavesensitive), and then passed the password as a SetValue statement in the SQL Agent job. It still fails, but this time it is during the reading of the Machine.config (I am presuming for the Script task). For now, I've resolved to working with the DBAs to set up SQL Server to use local accounts permissioned appropriately.

EDIT: This did fix the problem. By using an actual non-fixed account I was able to get SQL Agent to work as expected using SaveSensitiveWithPassword in no time at all. Hopefully a help to someone else out there who runs across this...