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

SSIS Deployment Problems Access denied

I have 2 SSIS packages that don’t deploy correctly to the SSIS server.

One package renames the files to conform for a data import from csv files. It shows that it executes successfully but doesn't change any files, when executed from MSDB or the SQL agent. The dtsx executed from the deployed folder does work.
The second is an Import dtsx which retrieves data from csv files and loads them into a intermediary database and then executes an sp to load into the database then archives the files. The problem returned when I attempt to deploy it to the SSIS server is the connection to the database is denied.
I have tried the common SQL Authenitication client, and my network profile with windows authentication and it is denied everytime to the underlying database.
The proxy for the agent is based on my credentials (My profile is the only one that has both the SQL server and the Network rights to perform the actions).
Both packages execute properly in the development environment, and the dtsx's both execute properly.

The first completes without error, but does nothing.
The second deploys with the error that the underlying database denies access.
0
chtullu135
Asked:
chtullu135
  • 4
  • 3
1 Solution
 
Jason Yousef, MSSr. BI DeveloperCommented:
Ok, it's obvious that it's a permission issue since the 2 packages works fine in BIDS.
You also need to differentiate between the words 'Deployed' and 'executed', deployed means attaching it to the sql server SSIS store or in the server's file system to be ready for execution.

SO where you're having trouble with deploying to the server or executing it on the server?
0
 
chtullu135Author Commented:
Basically the problem is that the package will not execute via the job agent but can be executed manually from the sql server.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
ok, so now check the SERVICE ACCOUNT that the sql agent job is using, it needs to be an account that has access to the server and the files that you're connecting to.

Also make sure to restart the service after you change the account.

first try to use your account to make sure that it's the issue, then try to configure the proxy again, use this reference

http://msdn.microsoft.com/en-us/library/ms189064%28v=sql.90%29.aspx
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
chtullu135Author Commented:
I've verified that the service account has access to the files.  In fact the Proxy uses the same credentials as when used to execute the packages from msdb.  The files use the following naming convention.  $RFI:R:\
0
 
chtullu135Author Commented:
The problem was resolved by using the file UNC file path
0
 
chtullu135Author Commented:
Your advice put me on course
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Thanks for the grade and points, glad that you fixed your problem.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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