I have a SSIS package which performs a few tasks using SQL Server Agent. First it executes a script task, then accesses Excel files, executes a SQL query and moves files to another directory.

The package worked fine when I executed it manually via debugger.

When I deployed the package and created an Agent job it fails on the excel connection task.

My implementation: - Excel files are located on a share stored on another account in the domain - the account which executes the package and runs agent jobs has permissions in the share and there is a proxy created for the credentials in the database

What are your suggestions to try? I spent a lot of time trying to fix it.

  • Encrypt the ssis package with "encrypt all with password". – Kin Shah Nov 28 '14 at 13:32
  • Why should I encrypt the package? Wouldn't it make the problem even worse? – BuahahaXD Nov 28 '14 at 13:53
  • Nope.. Encrypt and run as a cmd line job from SQL agent. – Kin Shah Nov 28 '14 at 13:58
  • Perhaps it's my ignorance, but I don't see how changing your package protection level is going to benefit this. It doesn't appear to be an issue related to a connection manager which uses stored credentials – billinkc Dec 1 '14 at 16:08
  • What I would propose is identifying what specifically is failing. It sounds like there a few different candidates for failure in play here: Network share - are you using a UNC path \\server\share or a mapped drive `S:` Since this is Excel, you are running the job in 32 bit mode, right? How does it access the Excel file - read or write? If you make a similar package that just attempts to access the directory (foreach file enumerator), does it "see" the files there (this can help identify permission issues) – billinkc Dec 1 '14 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.