May 14, 2012 at 12:45 pm
Hi all - I'm really green with SSIS and I've inherited a partially complete dtsx package and have been tasked with adding some error handling. Most of what they need right now is checking that files exist in various paths (different txt/csv connection managers) prior to attempting to operate on them, then sending an error email after the package completes. Right now, I'm using a script task (shown below), then an execute sql task to log errrors to a table from which I'll construct the error/warning email.
This method seems to require that I make a script task or each sequence container in the package (there are 8). The scripts are nearly identical so what I'd like to do is pass in the <connectionName> parameter and just have one script, or to rewrite this as a CLR function, or just in SOME way make this more generic and easier to keep track of for depoloyment. Right now, each script looks like it is getting it's own namespace with a GUID. How the heck could I keep those straight as we go from dev to UAT to prod?
ANY help getting me down the right path is GREATLY appreciated.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_974af6fc0d814ba6a93815a1e944373f.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
string curFile = "";
foreach (var conn in this.Dts.Connections)
{
//the next line is currently hard coded but
//would like to pass in parameter here
if (conn.Name == <param>)
{
//Console.WriteLine(conn.ConnectionString);
curFile = conn.ConnectionString;
if (File.Exists(curFile))
{
Dts.TaskResult = (int)ScriptResults.Success;
return;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
}
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
}
}
May 15, 2012 at 6:07 pm
I can appreciate the proactive nature of what you're trying to do but I could suggest a different approach. Implement an OnError Event Handler at the package level that logs to your exceptions table and configure your Precedence constraints such that when a Task containing a file operation fails to find a file it moves on to the appropriate Task in the flow.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2012 at 4:47 pm
opc.three -
you've helped me before and I really appreciate you and all the good folks on this forum. sorry it took so long for me to get back, but I've been in fire-fighting mode the last couple of days.
Anyway, I appreciate your suggestion, but I didn't see how a package level handler would be able to tell me which sequence container the error came from. You DID give me food for thought and I was able to create a control level error handler that logs where the error came from. I need to play with it more and haven't had time, but off the top of your head, is there anyway to get additional error information out of it - like "file not found", or "data conversion error occured" or things of that nature?
Thanks again and I'll keep you posted as I muddle through this.
Kevin
May 18, 2012 at 3:43 pm
Uripedes Pants (5/17/2012)
opc.three -you've helped me before and I really appreciate you and all the good folks on this forum. sorry it took so long for me to get back, but I've been in fire-fighting mode the last couple of days.
Anyway, I appreciate your suggestion, but I didn't see how a package level handler would be able to tell me which sequence container the error came from. You DID give me food for thought and I was able to create a control level error handler that logs where the error came from. I need to play with it more and haven't had time, but off the top of your head, is there anyway to get additional error information out of it - like "file not found", or "data conversion error occured" or things of that nature?
Thanks again and I'll keep you posted as I muddle through this.
Kevin
Hey Kevin, sorry for the delay in getting back. I wanted to check my facts before I responded.
Here is what I was talking about:
- Create an Event Handler at the Package-level for the OnError event.
- Add a Script Task to the Event Handler.
- Add System::SourceName to the ReadOnlyVariables of the Script Task.
- Within the script in your Event Handler's Script Task the variable will contain the name of the Task within your Sequence Container that raised the error and you can reference it this way:
string errorTaskName = Dts.Variables["System::SourceName"].Value.ToString();
Now, if you want to know the Sequence Container's name that contained the Task that raised the error, that might be a different story. We can access the GUID of the parent container as System::SourceParentGUID, but I have not been able to determine a way to resolve that GUID to the container's name, which in this case would be your Sequence Container's name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 18, 2012 at 4:10 pm
Hmmm... more food for thought. I'm not so much interested in getting the name of the sequence container, but what I am interested in the the error message that I log and how to handle different ones. This package processes 16 text files. Some, it doesn't really matter if they are there or not and processing should continue after logging a warning that the file was not there. Others, need to be treated as fatal and should log a Fatal Error message, then jump out to the build/send email component. Most non-fatal errors should exit thier sequence container and continue to the next. Some though will need to return to the next item in their containers.
What I'm worried about is returning to the proper task after jumping out to a package level event - yes, old time speghetti code in a modern environment 😀
I'm giong to get back to this project on Monday. Now's the time to fill out my &%$^% time-sheets and get outa here!
Thanks again for the help, and I'll try not to ask too many obviously ignorant questions.
Kevin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply