Solved

SSIS Deployment Problems Access denied

Posted on 2012-03-29
7
336 Views
Last Modified: 2012-08-13
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
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 37786795
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
 

Author Comment

by:chtullu135
ID: 37787453
Basically the problem is that the package will not execute via the job agent but can be executed manually from the sql server.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37787637
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:chtullu135
ID: 37788013
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
 

Author Comment

by:chtullu135
ID: 37844399
The problem was resolved by using the file UNC file path
0
 

Author Closing Comment

by:chtullu135
ID: 37857646
Your advice put me on course
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37857652
Thanks for the grade and points, glad that you fixed your problem.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question