scheduled SSIS Package Doesn't run

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

scheduled SSIS Package Doesn't run

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-11 : 11:29:13

Hi all,
I have an SSIS package that loops through files in a folder then emails the file as an attachment,then move the file to another folder.

When I run the package in visual studio everything works ok.
I then schedule this to run in SQL Server by creating a new job
Type SQL Agent Service Account - File system.

My problem is that when I run the job SQL says JOB succeeded.
Yet NOTHING has happened.
ie: no email sent and no file move.
If I run the package again in Visual studio then all is ok.
Has anyone ever seen this type of behaviour?
Thx,
Ray..

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-12 : 12:59:54

What does the log file say when run by the agent?
What is the source folder? UNC path? Specified in configuration?
Is the source folder empty? Or not accessible by the Agent service account?
Can you create a proxy and run the step as you? - to see if it is a permission issue.

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-12 : 13:27:04

Hiya and thx for replying.
This looks like something to do with the fact that I am mapping to a remote server.?When I schedule the task in sql server for the local drive it does work
Here is the story with 3 test situations
1 is the local server
2 is a remote server mapped as drive M:3 is a different remote server mapped as drive x:
(1)SSIS package called Testlocal uses c:\test,
runs the package from Visual Studio - OK
run the package from a scheduled job in SQL Server - OK

(2)SSIS package called Testremote uses m:\test,
runs the package from Visual Studio - OK
run the package from a scheduled job in SQL Server
job runs successfully but no emai is sent and no file is moved

(3)SSIS package called Testremote2 uses x:\test,
runs the package from Visual Studio - OK
run the package from a scheduled job in SQL Server
job runs successfully but no emai is sent and no file is moved

So when I try use a mapped drive rather than local scheduled JOB in SQL server says success but nothing happens ?

Please help
Ray..

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-12 : 17:50:11

1. Are there 3 separate packages? Or one package with different configurations?

2. Are you using the send mail task? Or are you sending email from a script task?
3. Have you enabled logging? What is in the log file when run from the servers?
Need more information.

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-13 : 05:28:31

Hiya,
These are 3 seperate packages.
Yes I am using the send mail task and then a file system task to move the file.
Only the remote package works when schedule from SQL server as a JOB ?
Log is enabled in SQL but it says that the Jobs ran successfully?
Yet for Testremote and Testremote2 no email is sent and no file is moved ?
Thx,
Ray..

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-13 : 09:36:07

Ray,
You need to look into the log file that the package creates, not the SQL Agent job step.
Can you capture the OnError, OnPostExecute, OnWarning, Diagnostic events?

Are you using a proxy for running this step? Or the SQL Agent account? Is it a domain account?
Can you check that the account has permission on the folder? To send email?

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-13 : 12:42:20

Hiya and thx for sticking woth me.
I have enabled logging on SSIS and it has shown something.
When I run the package in SSIS - all ok.
When I run the package as a scheduled job - it saus success.
however the log file says
"The For Each File enumerator did not find any files that matched the file pattern,or the specified directory was empty" ????
But the collection is set to say x:\test and there are files in there
in the form ORD999999001.. I thought this might be dues to the fact that there is no file ext but I also tried renaming the file to ORD999999001.csv but the same error.
I have also tried ord* , ord*.csv , *.* and all say the same error?

My source is x:\test destination is x:\test\moved
x is a remote server.
If I try C:\test destination c:\test\moved all works
c is local drive of sql server

Any ideas ?
Agent service account ?what is this?
Do I need to add further permissions on x:\test folder
log doesn't say anything about permssion denied just file not found

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-13 : 13:02:23

Just another note.
I added everyone to the permissions of the share x:but still "File not found error"?

Ray..

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-14 : 04:32:31

Instead of x:\test use \\ServerName\Share\ (And share the folder on the remote server)

When the package runs from SQL Agent, it be run under the security context of the SQL Agent service account.
It is possible this account does not have an x: drive mapping. Or its x: drive is mapped to a different folder.

To check the Agent account name, either
- use SQL Server Configuration Manager
- In the Activity Monitor (in management studio) look for the loginname against any application starting with "SQL Agent"

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-08-14 : 10:39:21

Yes !!! Thats it
\\server\folder worked !!

One final Question.
I have given everyone permissions on this folder.
If I remove everyone and just leave administrator it fails!!
What account to I need to give permssions?
Thx a mil
Ray..

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-14 : 11:01:20

SQL Agent service account
And maybe the SQL Server service account (depending)

Or if the step is running under a proxy account, then the proxy needs permission

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources