Schedule SSIS package in SQL Server 2005

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?
audreymjhhaAsked:
Who is Participating?
 
Christopher GordonSenior Developer AnalystCommented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
audreymjhhaAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Christopher GordonSenior Developer AnalystCommented:
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
 
audreymjhhaAuthor Commented:
How do I know the which service account runs SQL Agent? How do I check the permission access?
Thank you
0
 
Christopher GordonSenior Developer AnalystCommented:
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
 
audreymjhhaAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
audreymjhhaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.