meta data for a report

• cjp

  Newbie

  Points: 2

  More actions

  January 24, 2003 at 2:29 am

  #105615

  Does anyone know if it is possible to get the source and destination column metadata information into a report?

  i.e. for a column which has been populated using a transformation task I can view the source columns involved in the transformation under the meta data area of DTS. But does SQLServer provide objects/methods for accessing this information from VB etc.

  Many thanks

  CJP

• January 27, 2003 at 8:00 am

  This was removed by the editor as SPAM

• philcart

  SSC-Forever

  Points: 47796

  More actions

  January 28, 2003 at 3:08 pm

  #447413

  There's nothing provided to extract any meta data.

  You could put together a little ActiveXScript to extract the info from the Transformations collection of the DataPump task.

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface

• cjp

  Newbie

  Points: 2

  More actions

  February 3, 2003 at 3:29 am

  #447789

  Thanks Phil,

  I'm probably missing the obvious but how do I get a handle on a DataPump task? I've been playing around with the repository API but I can't seem to figure it out.

  Many thanks

  Caroline

• philcart

  SSC-Forever

  Points: 47796

  More actions

  February 3, 2003 at 4:22 am

  #447796

  In your ActiveXScript, first you need a package object then you can open the steps and tasks associated with that package.

  EG:

  
  
  Dim oPkg, oStep, oTsk
  
  Set oPkg = DTSGlobalVariables.Parent ' Currentlt executing package.
  
  MsgBox oPkg.Name ' package name
  
  MsgBox oPkg.Steps.Count ' count of steps in package
  
  MsgBox oPkg.Tasks.Count ' count of tasks in package
  
  Set oStp = oPkg.Steps("<step name>") ' reference to a step within the package
  
  MsgBox oStp.Description ' step description
  
  Set oTsk = oPkg.Tasks("<task name>") ' reference to a task within the package
  
  MsgBox oTsk.Description ' task description
  
  Set oTsk = Nothing
  
  Set oStp = Nothing
  
  Set oPkg = Nothing
  
  

  Make sure you use the step and task names when writing the Set statement. Don't confuse it with the Description that is seen in the DTS Designer. If you're using SQL 2000 then Disconnected Edit can be very helpful to sort that out.

  Thanks

  Phill Carter

  Edited by - phillcart on 02/03/2003 04:24:12 AM

  --------------------
  Colt 45 - the original point and click interface