November 10, 2015 at 3:00 pm
hi all experts,
I created a SSIS package basically it's doing import process using excel connection. Now I'm trying to schedule this package.
the point is this package should not failed during the schedule time.
if someone forgot to close the excel file then package will be failed and give us an error at this time I want to send an email to
say "file is open, please close the file" Or in the package property if we don't change 64bits as false the package will not running
and give us an error then I want to send an email to say" please change the propertise".
Is there anyway to control these together?
Much appreciate If anyone can provide the detail code/script for this situation
November 11, 2015 at 12:34 pm
Have you looked at using the Error connector? It is not totally clear what you want to do but error handling in SSIS is pretty robust. What have you tried?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 12, 2015 at 8:30 am
I using below code to check if the excel file is opened or not
public void Main()
{
Boolean mFileLocked = true;
while (mFileLocked)
{
// Check if the file isn't locked by an other process
try
{
// Try to open the file. If it succeeds, set variable to false and close stream
System.IO.FileStream mfs = new System.IO.FileStream(Dts.Variables["User::varmFilePath"].Value.ToString(), System.IO.FileMode.Open);
mFileLocked = false;
mfs.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (UnauthorizedAccessException ex)
{
// If opening fails, it's probably locked by an other process
mFileLocked = true;
// Wait two seconds before rechecking
System.Threading.Thread.Sleep(2000);
}
catch (Exception ex)
{
// Catch other errors, like file doesn't exists
Dts.Events.FireError(0, "Wait until m file is released", ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
break;
}
}
}
But this code is not working fine. IF file is opened I want to send an email
November 12, 2015 at 8:40 am
caojunhe24 (11/12/2015)
I using below code to check if the excel file is opened or notpublic void Main()
{
Boolean mFileLocked = true;
while (mFileLocked)
{
// Check if the file isn't locked by an other process
try
{
// Try to open the file. If it succeeds, set variable to false and close stream
System.IO.FileStream mfs = new System.IO.FileStream(Dts.Variables["User::varmFilePath"].Value.ToString(), System.IO.FileMode.Open);
mFileLocked = false;
mfs.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (UnauthorizedAccessException ex)
{
// If opening fails, it's probably locked by an other process
mFileLocked = true;
// Wait two seconds before rechecking
System.Threading.Thread.Sleep(2000);
}
catch (Exception ex)
{
// Catch other errors, like file doesn't exists
Dts.Events.FireError(0, "Wait until m file is released", ex.Message, string.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
break;
}
}
}
But this code is not working fine. IF file is opened I want to send an email
Can you define "not working"? I don't see in here anything to send an email. Or do you have a send email task on error of this step?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 12, 2015 at 9:44 am
I have one script task at top of the package to check if there are any excel file exist in two different folders . If no file exist i will do normal process otherwise I will check if that excel file is opened or not.
when I paste this code into my script task to check if it's opened or not the script task is running like forever -no result come out.
the result what I want to is if excel file is open(we can create a variables to hold this value) then the next task will be send email using this variable(expression) otherwise go to next step. I'm planning to create another send email task after that but is that possible to do these steps in one script task?
November 12, 2015 at 9:58 am
caojunhe24 (11/12/2015)
I have one script task at top of the package to check if there are any excel file exist in two different folders . If no file exist i will do normal process otherwise I will check if that excel file is opened or not.when I paste this code into my script task to check if it's opened or not the script task is running like forever -no result come out.
the result what I want to is if excel file is open(we can create a variables to hold this value) then the next task will be send email using this variable(expression) otherwise go to next step. I'm planning to create another send email task after that but is that possible to do these steps in one script task?
Why do you have this inside a while loop? The way you have this coded it will stay inside that loop until the file is closed. I always cringe when I see a loop that is basically while(True).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply