• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

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?
0
audreymjhha
Asked:
audreymjhha
  • 5
  • 4
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now