Solved

Schedule SSIS package in SQL Server 2005

Posted on 2009-05-07
9
822 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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