Post reply

Access DTS Global var from within called sp

  • 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.

  • September 11, 2003 at 10:46 am

    #473761

    Check out this article at SQLDTS.COM.

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

    Greg

  • 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?

  • 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

  • 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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply