Access DTS Global var from within called sp

• phil_novice

  SSC Enthusiast

  Points: 133

  More actions

  September 11, 2003 at 3:43 am

  #106222

  Hi,

  I have a DTS Package with some Global Varibles, one of the Tasks in the DTS Package is to execute a stored procedure via an Execute SQL Task.

  Is there anyway in the Stored Procedure I can access the values held in the parent DTS Package?

  Thanks.

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  September 11, 2003 at 10:46 am

  #473761

  Check out this article at SQLDTS.COM.

  [http://www.sqldts.com/?234]

  Greg

• phil_novice

  SSC Enthusiast

  Points: 133

  More actions

  September 15, 2003 at 10:15 am

  #474214

  Hi,

  Thanks for that, but I'm using a SQL 7 Server, this looks like it was a new feature for SQL 2000.

  Is there anyway of doing the same thing in SQL Server 7?

• philcart

  SSC-Forever

  Points: 47794

  More actions

  September 15, 2003 at 6:00 pm

  #474282

  You can do something similar but it would require a short ActiveX Script task before the ExecuteSQL task. This script would dynamically re-write the SQL of the Execute SQL task.

  Here's a quick example, no guarantees this will work as I don't have SQL7

  
  
  Const TASK_NAME = DTS_ExecuteSQL_Task1
  
  
  
  Function Main()
  
  On Error Resume Next
  
  
  
  Dim oTsk ' DTS Task object
  
  Dim sSQL ' SQL string to execute
  
  
  
  ' open DTS task object so we can get to the properties
  
  Set oTsk = DTSGlobalVariables.Parent.Tasks(TASK_NAME
  
  
  
  ' build SQL statement calling stored procedure that extracts error code and message
  
  sSQL = "EXEC dbo.usp_Whatever "
  
  sSQL = sSQL & "'" & DTSGlobalVariables("gvWhatever") & "'"
  
  
  
  oTsk.SQLStatement = sSQL
  
  
  
  Set oTsk = Nothing
  
  Main = DTSTaskExecResult_Success
  
  
  
  End Function
  
  

  Hope this helps

  Phill Carter

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

  Colt 45 - the original point and click interface

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

• phil_novice

  SSC Enthusiast

  Points: 133

  More actions

  September 16, 2003 at 3:25 am

  #474342

  Hi

  Thanks for that, had a quick test and got it working, but not sure it's worth doing this way. I think I may hard code the value in the stored procedure and document the fact.

  Thanks again.