?
Solved

Schedule SSIS package in SQL Server 2005

Posted on 2009-05-07
9
Medium Priority
?
826 Views
Last Modified: 2012-05-06
I created and deployed an SSIS package. I imported it into SQL Server 2005. I can right click and execute the job successfully. However, I am unable to create a job that runs successfully with the SSIS package. Any ideas why the SQL job cannot execute the package?
0
Comment
Question by:audreymjhha
[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
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 24328888
What are the error message(s) when you select "View History" by right clicking on the job name?

Are you executing the job (under SQL Agent) or are you executing the package (within Integration Services in SSMS)?
0
 

Author Comment

by:audreymjhha
ID: 24335692
I'm trying to run the package under SQL Agent job. Right now I decided to just create an ssis that copies a file my computer to a server. It runs perfectly within SSIS and if I right click on the actual package within SQL server management studio, it runs. When I created a SQL Server Agent Job - I receive the following error.
Source: Package Connection manager "connect"     Description: The file name "\\lennon\connect" specified in the connection was not valid.  End Error  Error: 2009-05-08 09:00:22.96     Code: 0xC001401D     Source: Package      Description: Connection "connect" failed validation.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 24335993
Could you double check that the service account running SQLAgent on your SQL Server Database has permissions/access to the file name and path specified in your connection?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:audreymjhha
ID: 24336181
How do I know the which service account runs SQL Agent? How do I check the permission access?
Thank you
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 24336341
Control Panel...
Administrative Tools...
Services...

Look for SQL Server Agent (MSSQLServer)
The account specified in the field (Log On As) is the account running SQLAgent (which runs your Jobs)

For testing purposes, try placing the file on the SQL Server box,
change the connection string for your file in your SSIS package to point to the new location
update your package in Integration Services
run the job.

My guess is that your SQL Agent is running as "Local System" and that account does not have access to " \\lennon\connect".
0
 

Author Comment

by:audreymjhha
ID: 24337069
You are correct - the SQL Agent is running as "Local System". I created the SSIS package on the same server as the SQL Server. Your theory is correct - how can I get around this. I tried to create a Proxy account but I was unsuccessful.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 24337167
I've addressed this permissions issue by changing the account running SQL Agent from  from Local System to a domain account that has appropriate permissions to the required network resources such as a file share.  I
0
 

Author Comment

by:audreymjhha
ID: 24337663
Is there another way around this because I don't want to alter that setting because other production jobs use it. Should I create a Proxy account? Have you created one?
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 2000 total points
ID: 24338280
Here is a link to accounts and SQLAgent options:
http://msdn.microsoft.com/en-us/library/ms191543.aspx

If you don't want to change the account, you could always use the server itself as the drop zone for the files you are trying to process.  

Again, the way i've set this up before in the past is to use a Windows Account with Admin permissions on the local machine and then grant that windows account appropriate permissions on other network resources.  If your SQL Agent service is currently running under "Local System" I can't really think of what would cause problems by changing to Windows Account with Local Admin permissions.  Anyways, the link would do a better job explaining than me.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

764 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