Hi all..Am new to DTS..Using SQL Server 7.0 and I am tring to pass a string to a global variable to check if a directory exists..

I have declared gv_ExportFilePath in the package properties as String and the is nothing in the value property..I have an ActiveX Script Task that is as follows:

Option Explicit

Function Main()
    Dim objFileSysObject
    Dim tMessage

    ' Display the value passed into global variable gv_ExportFilePath.
    tMessage = DTSGlobalVariables("gv_ExportFilePath").Value
    MsgBox tMessage

    ' Create the File System Object.
    Set objFileSysObject = CreateObject("Scripting.FileSystemObject")

    ' Check if folder passed into gv_ExportFilePath exists.
    If objFileSysObject.FolderExists(DTSGlobalVariables(" gv_ExportFilePath").Value) Then
        tMessage = "Yeah .. " & DTSGlobalVariables("gv_ExportFilePath").Value & " exists"
        MsgBox tMessage

        Main = DTSTaskExecResult_Success

    Else
        tMessage = "Ooops .. " & DTSGlobalVariables("gv_ExportFilePath").Value & " does not exist"
        MsgBox tMessage

        Main = DTSTaskExecResult_Failure

    End If

    Set objFileSysObject = Nothing

If I run the package from within the package I get the expected results of a failure..When I change the value property of the global variable to a valid path, I get the expected results of a success..

The problem I am having is when I set up a stored procedure to run this package, I am getting a failure reported no matter what value I pass to the global variable.. I have included my stored procedure as well:

CREATE PROC sp_ExportRegistrationData
  @p_tServer varchar(255),
  @PkgName varchar(255),
  @p_tServerPWD varchar(255) = Null,
  @PkgPWD varchar(255) = '',
  @p_tExportPath varchar(511) = NULL

AS
  SET NOCOUNT ON

  DECLARE @iHResult integer
  DECLARE @ret int
  DECLARE @oPKG int
  DECLARE @tCommand varchar(1000)
  DECLARE @GVName varchar(255)
  DECLARE @GVValue varchar(255)
  DECLARE @GVOutput varchar(255)

  EXEC @iHResult = sp_OACreate 'DTS.Package', @oPKG OUTPUT
  IF @iHResult <> 0
    BEGIN
      PRINT '*** Create Package object failed'
      RETURN 1
    END

  SET @tCommand = 'LoadFromSQLServer("' + @p_tServer +'", "' + SUSER_SNAME() + '", "' + @p_tServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
  EXEC @iHResult = sp_OAMethod @oPKG, @tCommand, NULL
  IF @iHResult <> 0
    BEGIN
      PRINT '*** LoadFromSQLServer failed'
      RETURN 1
    END

  IF @p_tExportPath IS NOT NULL
    BEGIN
      EXEC @iHResult = sp_OASetProperty @oPKG, 'GlobalVariables("gv_ExportFilePath").Value', @p_tExportPath
      IF @iHResult <> 0
        BEGIN
          PRINT '*** GlobalVariable 1 Assignment Failed'
          RETURN 1
        END
    END

  EXEC @iHResult = sp_OAMethod @oPKG, 'Execute'
  IF @iHResult <> 0
    BEGIN
      PRINT '*** Execute failed'
      RETURN 1
    END

  EXEC @iHResult = sp_OAMethod @oPKG, 'UnInitialize'
  IF @iHResult <> 0
    BEGIN
      PRINT '*** UnInitialize failed'
      RETURN 1
    END

  EXEC @iHResult = sp_OADestroy @oPKG
  IF @iHResult <> 0
    BEGIN
      PRINT '*** Clean up failed'
      RETURN 1
    END

GO

If there is anymore detail required, please don't hesitate to ask..

Thanks in advance,

Shane

check this


http://www.sqldts.com/default.aspx?211

Jaime E. Maccou