Programmatically Add/Customize SSIS built in Logging - Not getting expected output

• vinod.rajput

  Valued Member

  Points: 69

  More actions

  September 22, 2008 at 3:50 am

  #124736

  Hi,

  Please anybody help me to resolve the problem related to SSIS logging.

  I want to customize SSIS logging . I have created one SQL script Task which contains the following code ......

  public void Main()

  {

  try

  {

  CreatePackageLogProvider();

  Dts.TaskResult = (int)ScriptResults.Success;

  }

  catch (Exception ex)

  {

  MessageBox.Show("Error in script: " + ex.Message);

  }

  }

  public void CreatePackageLogProvider()

  {

  try

  {

  Package p = new Package();

  ConnectionManager dbConMgr = p.Connections.Add("OLEDB");

  dbConMgr.Name = "SQL2008.BE_ETLDB";

  dbConMgr.ConnectionString = @"Data Source=WPNQ1109104448\SQL2008;User ID=appwriter;Password=fastwriter;Initial Catalog=BE_ETLDB;Provider=SQLNCLI10.1;Persist Security Info=True;";

  LogProvider logProvider = p.LogProviders.Add("DTS.LogProviderSQLServer");

  logProvider.ConfigString = "SQL2008.BE_ETLDB";

  p.LoggingOptions.SelectedLogProviders.Add(logProvider);

  p.LoggingOptions.EventFilterKind = DTSEventFilterKind.Inclusion;

  //p.LoggingOptions.EventFilter = new string[] { "OnInformation", "OnProgress", "OnWarning", "OnError" };

  //p.LoggingOptions.EventFilter = new string[] {"PackageStart","PackageEnd","OnInformation", "OnProgress", "OnWarning", "OnError" };

  p.LoggingOptions.EventFilter = new string[] { "OnInformation", "OnWarning" };

  DTSEventColumnFilter ecf = new DTSEventColumnFilter();

  // Set the detailed information to log when the event occurs.

  // This specifies to log the Computer, Operator, and SourceName only.

  ecf.Computer = true;

  ecf.Operator = true;

  ecf.SourceName = true;

  ecf.SourceID = false;

  ecf.ExecutionID = false;

  ecf.MessageText = true;

  ecf.DataBytes = false;

  // The event is the first parameter, and the columns to log is the enumeration.

  //p.LoggingOptions.SetColumnFilter("OnError", ecf);

  p.LoggingOptions.SetColumnFilter("OnWarning", ecf);

  //p.LoggingOptions.SetColumnFilter("OnProgress", ecf);

  p.LoggingOptions.SetColumnFilter("OnInformation", ecf);

  p.LoggingMode = DTSLoggingMode.Enabled;

  string[] strArr = p.LoggingOptions.GetColumnFilteredEvents(); // Just for testing

  DTSEventColumnFilter newECF = new DTSEventColumnFilter();

  p.LoggingOptions.GetColumnFilter("OnInformation", ref newECF);

  p.LoggingOptions.GetColumnFilter("OnWarning", ref newECF);

  logProvider.OpenLog();

  bool fireAgain = false;

  Dts.Events.FireInformation(1, "Fire events", "FireInformation from script", "", 0, ref fireAgain);

  p.Execute(); // It must be executed

  Dts.Events.FireWarning(2, "Warning component", "Warning generated from script", null, 0);

  Dts.Events.FireInformation(2, "Fire events", "FireInformation from script", "", 0, ref fireAgain);

  //logProvider.CloseLog(); // should be call at the end of package execution

  }

  catch (Exception ex)

  {

  throw ex;

  }

  }

  This code is executing successfully and 'sysssislog' table is also created in BE_ETLDB database. But the problem is that even if I have set event filter for OnWarning and OnInformation , it is only logging for 'PackageStart' and 'PackageEnd'

  events , so after executing this package the sysssislog table just contains 2 records. However I want to log user specified events. Actaully I want to add event filter for built in SSIS logging ,so that user can set the that filter through SSIS configuration table.

  Please let me know Am I doing anything wrong with the aboce code ?

  --------------------------------------------------------------------------------

  Vinod Rajput