Referring to tasks from inside an SSIS Script Task

• Ray Cacciatore

  Ten Centuries

  Points: 1260

  More actions

  April 3, 2011 at 12:15 am

  #234163

  Is it possible to refer to other tasks' properties from inside an SSIS Script Task?

  For example, our old DTS package had an ActiveX script after every task that logs a trace of each task in a SQL Server table. Something like this:

  Set objStep = DTSGlobalVariables.Parent.Steps("ExecuteSQL_GetNewTraceID")

  DTSGlobalVariables("cDtsName").Value = DTSGlobalVariables.Parent.Name

  DTSGlobalVariables("cStep").Value = "01"

  DTSGlobalVariables("cTaskName").Value = objStep.TaskName

  DTSGlobalVariables("dtStart").Value = objStep.StartTime

  DTSGlobalVariables("dtEnd").Value = objStep.FinishTime

  DTSGlobalVariables("intResult").Value = objStep.ExecutionResult

  DTSGlobalVariables("cDetail").Value = objStep.Description

  DTSGlobalVariables.Parent.Steps("ExecuteSQL_TraceEvent").execute

  DTSGlobalVariables("cTask").Value = "ActiveScript_TraceStep_01"

  If DTSGlobalVariables("intResult").Value = 0 Then

  Main = DTSTaskExecResult_Success

  Else

  Main = DTSTaskExecResult_Failure

  Set objDtsPkg = DTSGlobalVariables.Parent

  Set objStepPkg= objDtsPkg.Steps("ActiveScript_FinDts")

  objStepPkg.Execute

  End If

  In other words, the script would point to the previous step, get its name, start time, end time, its result, and insert this information into a table in SQL Server. The step called ExecuteSQL_TraceEvent is the task that inserts the record.

  I'm trying to do this in SSIS but I'm getting the feeling that I cannot read or fetch the properties of other tasks from inside a Script Task. Can someone point me into the right direction on how to do this in SSIS?

  Thanks !

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  April 3, 2011 at 9:29 pm

  #1306844

  This sort of script task is not rquired in SSIS. Doing this sort of thing with SSIS is, in fact, generally not possible.

  Instead, SSIS gives you this logging functionality out of the box. You can configure the package to log the start/end time of each task simply by configuring logging at the package level. This can be overidden at the task level if you desire. In SQL 2005, if you log activity to a database, logging goes to table dbo.sysdtslog90.