SSIS Error handling using Script Task

• Helpseeker

  Right there with Babe

  Points: 782

  More actions

  May 5, 2016 at 4:31 am

  #316281

  Hi,

  Am new to SSIS. Am using Data flow task to move data from source table to destination table.

  I need to capture the error description, task name and Package name and store it in error log table.

  currently am achieving it through Execute SQL task.But i want to use Script Task and store the error in the error log table.

  Can some one help me to get this?

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323504

  More actions

  May 5, 2016 at 6:45 am

  #1876810

  this link has a couple of screenshots and an explanation that can help:

  http://stackoverflow.com/questions/5772667/how-to-create-an-error-log-or-custom-error-log-within-an-ssis-package

  basically, SSIS has a built in error logging mechanism, and you can have it write to one or more locations, including a specific database.

  so you could have it write to a file, a databas eand the windows event log, all at the same time.

  Inside the script task itself, you can raise errors via code for "logical errors", or to log more detaila bout the error to help with debugging.

  a crappy example below:

  try

  {

  //--doing a bunch of work here

  if(Datatable.Rows.Count==0)

  {

  //Raise my own logical event

  Dts.Events.FireError(0, "Error in proc ProcessSpecialCSVFiles: ", "The datatable returned Zero Rows, when the expectation is there is always data, "", 0);

  }

  return true;

  }

  //catch all errors and log them!

  catch (Exception ex)

  {

  Dts.Events.FireError(0, "Error in proc ProcessSpecialCSVFiles: ", ex.Message, "", 0);

  return false;

  }

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!