Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS Deployment Problems Access denied

Posted on 2012-03-29
7
Medium Priority
?
344 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 2000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

609 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