SSIS @[System::ErrorDescription]

• DONG YEN

  SSC Veteran

  Points: 251

  More actions

  September 29, 2006 at 9:25 am

  #115868

  Hello,

  I would like to get the value of System variable "ErrorDescription" in a table, by implementing a SQL query into a SQL Task with OnError event.

  The SQL expression is:

  "INSERT INTO LOG(EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, ContainerDuration,InsertCount, Host)

  VALUES ('OnError',

  ' " + @[System:ackageName] + "',

  '"+ @[System::SourceName] + "',

  0,

  '''+ (DT_TEXT, 1252)@[System::ErrorDescription] + ''',

  0,

  0,

  0,

  'Toto'

  )"

  But I've got the text following : "(DT_TEXT, 1252)@[System::ErrorDescription]" inside my column EventDescription, instead of the value of this variable.

  This column's type is Text.

  Thank you for helping me to change this query.

  Marie-Thérèse

• d_george

  SSCrazy

  Points: 2462

  More actions

  October 2, 2006 at 5:50 am

  #663550

  I need to know something similar.

  Did you happen to find an answer?

  Actually it looks like you are using Jamies way - http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

  You should be able to use this then (I added some stuff to the EventDescription):

  "INSERT INTO [SSISLog]

  ([EventType],[PackageName],[TaskName],[EventCode],[EventDescription],[PackageDurationSeconds],[ContainerDurationSeconds],[Host])

  VALUES ('OnError',"

  + "'" + @[System:ackageName] + "'"

  + ",'" + @[System::SourceName] + "'"

  + ","

  + (DT_STR, 15, 1252)@[System::ErrorCode]

  + ",'" + @[System::SourceName]  + "  " + @[System::TaskName]  + "  " + @[System::ErrorDescription] + "'"

  + ","

  + (DT_STR, 60, 1252)DATEDIFF("ss",@[System::StartTime],GETDATE()) + ","

  + (DT_STR, 60, 1252)DATEDIFF("ss",@[System::ContainerStartTime],GETDATE()) + ","

  + "'" + @[System::MachineName] + "'"

  + ")"