handling connection errors gracefully
I have an SSIS package that has a flat file connection manager with a path to a pc on our network like this \\deforest\logfile. If that particular computer is not up and running naturally my package fails. How can I capture errors in the connection manager, write them to a log and/or send an email and exit gracefully out of the package? Exact Error I am receiving: Validation error. DeForest Connection manager "Energy Data": The file name \\deforest\logfile specified in the connection was not valid. (I am using VS2008 Pro.)
August 9th, 2011 5:22pm

Use event handler in package and configure event handler to run onTaskfailed and in Event handler flow use send mail task to send mail see this thread http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e7a5e86b-bcfb-4bfe-9b70-822169cb747b/Shailesh , Please mark the post as answered if it answers your question.
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 5:39pm

I have used a send mail task before but I don't think it will even get that far. When I am debugging I receive an error message stating the package validation failed (see attached). I guess I don't understand how I implement a send mail taskfailed event for a connection manager. My control flow looks like this: I have a for each file enumerator which retrieves the path from the variable called varFilename then inside the for each file enumerator the data flow object called "Extract Data" is run which processes the file.
August 9th, 2011 6:07pm

Set to Delay Validation -->True in package properties and this is the validation error i am not much sure whether you can use onTaskfailed event in event handler in this case try to use OnError event . Shailesh , Please mark the post as answered if it answers your question.
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 6:19pm

Yeah, me either. I am going to try to select the top level executable and define an OnError event handler that contains a send mail task. I am also playing around with the DelayValidation setting on the connection in hopes it will delay the validation long enough to actually start the package and then in turn call the event handler when an error occurs. I am not sure if that will work yet or not. I have also contemplated writing a quick script task, which I could place before my For Loop to check the connection and save either a True (= failure) or False (all is ok) value to a variable and then maybe I can attached an event handler on that variable. I am going to try that next I suppose. I am rather new to SSIS so I have never done this before but I will keep you posted on what I eventually come up with. The reason why I need this: I need to log the error and simply exit gracefully out of the package because I have a "parent" package that contains a sequence container that runs the individual packages (one package for each plant) one by one so of course if one plant's connection fails the entire package fails and it does not continue reading the rest of the packages (the rest of the plants).
August 9th, 2011 6:40pm

So you would recommend I set the DelayValidation to False for the entire package? Then I would evaluate the event handler of the for loop?
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 7:00pm

If you are checking connection inside script task then you should use send mail from control flow only using precedence constraint . and DelayValidation should be TRUE if you want to make connection string dynamic else package will fail in validation phase if any validation error and will not reach till Script task so you'll not be able to send mail . i would suggest to use event handler for error handling see the below links you'll get some helps about event handler http://msdn.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/ms141717.aspx Shailesh , Please mark the post as answered if it answers your question.
August 9th, 2011 7:13pm

I think I've got it resolved. In case anyone else is interested here is what I did: I inserted a script task above my for loop and called it "validate bridge computer connection". In the "validate bridge computer connection" script task I have one read only variable called varFileName (\\deforest\logfile) and one readwrite variable called blnSkipPlant (boolean) and I have the code shown below in the script (I hope this will only fail this one script task and not fail the "parent" or calling package..fingers crossed): Public Sub Main() Try If Dir(Dts.Variables("User::varFileName").Value, FileAttribute.Directory) = "" Then Dts.Variables("User::blnSkipPlant").Value = True Dts.TaskResult = ScriptResults.Failure Else Dts.Variables("User::blnSkipPlant").Value = False Dts.TaskResult = ScriptResults.Success End If Catch ex As Exception Dts.Variables("User::blnSkipPlant").Value = True Dts.TaskResult = ScriptResults.Failure End Try End Sub In my package properties I set the DelayValidation property = True. In my for loop I added a "Disable" expression: @[User::blnSkipPlant]. I added a send mail task under the Validate Bridge Computer Connection, OnTaskFailed event.
Free Windows Admin Tool Kit Click here and download it now
August 9th, 2011 11:01pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics