October 24, 2014 at 2:29 am
Hi Everyone,
I got a problem with SQL Server Agent Job. My job can't finished and keep stuck. I suspected that the job is stop on script task.
When I use BIDS, the package run successfully without any problem. When I run the package as a job using sql server agent, the job won't finish. It keep waiting and never end.
The script task I use is to collect some log files that I produced during my ETL process to be attached as email attachment. The send mail task is after the script task process.
My sending email task is running well (I know because I have some send mail task process before the one that stuck and all of them is using script task).
Anyone can help?
Thanks.
Here is my code:
public void Main()
{
// TODO: Add your code here
DateTime now = DateTime.Now;
bool isError = false;
int count = 1;
String today = now.ToString("dd_MM_yyyy");
String[] logFiles = new String[2];
logFiles[0] = @"C:\Batch\log\Fact_load_MA_initial_" + today + ".log" ;
if (Directory.GetFiles(@"C:\Batch\log\", "Fact_MA_error_initial_" + today + ".txt").Length > 0)
{
logFiles[count] = @"C:\Batch\log\Fact_MA_error_initial_" + today + ".txt";
isError = true;
count++;
}
String variable = "";
//System.IO.Compression.
Dts.Variables["User::Email_body"].Value = "Essbase_MA task has been finished One load log attached";
if(isError){
Dts.Variables["User::Email_body"].Value += " There is an error. Error message is attached";
}
foreach (String logFile in logFiles)
{
variable += logFile + "|";
}
Dts.Variables["User::Email_attachment"].Value = variable.Substring(0, variable.Length - 1);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
October 24, 2014 at 2:34 am
stopd87 (10/24/2014)
Hi Everyone,I got a problem with SQL Server Agent Job. My job can't finished and keep stuck. I suspected that the job is stop on script task.
When I use BIDS, the package run successfully without any problem. When I run the package as a job using sql server agent, the job won't finish. It keep waiting and never end.
The script task I use is to collect some log files that I produced during my ETL process to be attached as email attachment. The send mail task is after the script task process.
My sending email task is running well (I know because I have some send mail task process before the one that stuck and all of them is using script task).
Anyone can help?
Thanks.
Here is my code:
public void Main()
{
// TODO: Add your code here
DateTime now = DateTime.Now;
bool isError = false;
int count = 1;
String today = now.ToString("dd_MM_yyyy");
String[] logFiles = new String[2];
logFiles[0] = @"C:\Batch\log\Fact_load_MA_initial_" + today + ".log" ;
if (Directory.GetFiles(@"C:\Batch\log\", "Fact_MA_error_initial_" + today + ".txt").Length > 0)
{
logFiles[count] = @"C:\Batch\log\Fact_MA_error_initial_" + today + ".txt";
isError = true;
count++;
}
String variable = "";
//System.IO.Compression.
Dts.Variables["User::Email_body"].Value = "Essbase_MA task has been finished One load log attached";
if(isError){
Dts.Variables["User::Email_body"].Value += " There is an error. Error message is attached";
}
foreach (String logFile in logFiles)
{
variable += logFile + "|";
}
Dts.Variables["User::Email_attachment"].Value = variable.Substring(0, variable.Length - 1);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
Hard coding paths into Script Tasks like this is generally considered bad practice ...
Notwithstanding that, do all of these paths and folders exist on the server running the job?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2014 at 2:37 am
Phil Parkin (10/24/2014)
Hard coding paths into Script Tasks like this is generally considered bad practice ...
Notwithstanding that, do all of these paths and folders exist on the server running the job?
yes, of course. I also tried to run from BIDS on server that will run the job and it still running well. Is there any way to check which job that is running from sql server job?
October 24, 2014 at 2:56 am
Then it sounds like a permissions issue. Does the SQL Agent user have sufficient rights?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2014 at 3:02 am
Phil Parkin (10/24/2014)
Then it sounds like a permissions issue. Does the SQL Agent user have sufficient rights?
Yes. the sql server agent user is already same with the user that I test the package using BIDS on the server. As a windows user, the user also a part of administrator group, so for accessing the C: drive is not a problem.
October 24, 2014 at 3:14 am
Then I am running out of ideas. Problems like this are tough to debug.
You could try running the job in 32-bit mode (which is how it would have run in BIDS). Though I cannot see why that would help.
If that does not help, you could consider adding some sort of logging to the script to enable you to determine exactly which line is causing the issues.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2014 at 3:24 am
Phil Parkin (10/24/2014)
Then I am running out of ideas. Problems like this are tough to debug.You could try running the job in 32-bit mode (which is how it would have run in BIDS). Though I cannot see why that would help.
If that does not help, you could consider adding some sort of logging to the script to enable you to determine exactly which line is causing the issues.
hmm. I'll try the idea of running the job in 32 bit mode. maybe it can help.
I also feel confuse because this is a revision script. the older script has some more file to be attached and now I delete that part of script. Before I changed it, everything is running well. I have other 5 scripts like this (use to search file to be attached as email attachement) 3 of them is running well. There are 2 (including this one) that have the problem.
logging script? like writing to database or file to make sure that the script is running?
October 24, 2014 at 3:39 am
logging script? like writing to database or file to make sure that the script is running?
Yes, line by line, write to a file indicating that the line has been processed.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2014 at 3:44 am
Phil Parkin (10/24/2014)
logging script? like writing to database or file to make sure that the script is running?
Yes, line by line, write to a file indicating that the line has been processed.
Okay, I'll try that one also. I'll report the result later. Thanks for your advice
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply