Solved

Schedule SSIS package in SQL Server 2005

Posted on 2009-05-07
9
825 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
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: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 500 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

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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