Running SSIS Packages in Management Studio

We are using SQL Server 2005 Standard edition.We have the development studio installed on a developer’s workstation.The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server.I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I’m sure this is a permission based problem since it runs fine on my workstation.I tried logon onto the SQL Server with my id (I’m an admin to the server).I tried running the agent with my windows id.It looks like I’m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

Do you use config files and do not save sensitive? This should take care of this issue.

There are quite a few posts about it in this forum

Philippe

|||The problem I was getting occurred whether I used a config file or not. I had the package set to EncryptSensitiveWithUserKey. I created another thread to get production deployment suggestions. We don't want our DBAs using BIDS to edit SQL connection manager settings when they migrate SSIS packages to production.|||

Then don't let them. Use configurations and the deployment wizard.

-Jamie