Here's my dilemma.

I am using an active X script (data pump) in combination with a global
variable to assign a variable text file name as input to a DTS data
transform task that loads data from a .txt file to a table. Also, I
have a stored procedure wrapper that calls the dts job with a parameter
that it passes in to the global variable.

When I execute the DTS job by itself, I can tell that the data pump is
correctly picking up the global variable and using it as the source
file. In fact, it even writes over the source file name in the data
transform task.

I can also tell that the stored procedure is correctly passing in the
filename. I added a sql task to insert the filename into a table and
it is definitely getting the filename from the paramater in the stored
procedure.

However, the global variable passed in through the stored procedure
DOES NOT get used as the source file for the data transform task. I am
stumped. If anyone has a clue, please let me know.

Thanks -- JG

Here is the activeX script and the relevant stored procedure code:


'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Dim oPkg, oDataPump
Dim sSourceTable

Dim iConn
Set iConn = DTSGlobalVariables.Parent.Connections("Connection
1")


iConn.DataSource = DTSGlobalVariables("ipfile").Value

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("LoadLFDAuthnet").CustomTask

' Set the new values
oDataPump.SourceObjectName = DTSGlobalVariables("ipfile").Value


Set oDataPump = Nothing
Set oPkg = Nothing


Main = DTSTaskExecResult_Success
End Function

EXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUT
if @hr <> 0
BEGIN
print 'error create DTS.Package'
RETURN @hr
END

EXEC @hr = sp_OAMethod @object, 'LoadFromSQLServer', NULL,
@ServerName='xxxx.xxx.org',
@Flags=256,
@PackageName='lfd_authnetDP',
@ServerUserName='xxxx',
IF @hr <> 0
BEGIN
print '*** Load Package Failed'
EXEC sp_OAGeterrorinfo @object, @hr
set @errorString = '*** Load Package Failed'
RETURN @hr
END

EXEC @hr = sp_OASetProperty @object, 'GlobalVariables
("ipfile").value',@inputfile

IF @hr <> 0
BEGIN
print '*** Set Parameter Failed'
EXEC sp_OAGeterrorinfo @object, @hr
set @errorString = '*** Set Parameter failed'
RETURN @hr
END

EXEC @hr = sp_OAMethod @object, 'Execute'
IF @hr <> 0
BEGIN
print '*** Execute failed'
EXEC sp_OAGeterrorinfo @object, @hr
set @errorString = '*** Execute failed'
RETURN @hr
END

EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
print '*** Destroy Package failed'
EXEC sp_OAGeterrorinfo @object, @hr
set @ErrorString = '*** Destroy failed'
RETURN @hr
END

Here's what I was doing wrong:

The database server that held the DTS job was remote to my local
machine.

When I executed the DTS job "manually", it successfully found the
source file on my local server. When I called it from the stored
procedure, it was looking on the database server for the file and it
wasn't there!

I wanted to share my mistake in case anyone else was ever in this
situation.

Janet