source text file as input parameter from a stored procedure
- From: jgilmore@xxxxxxxxxxxxxxx
- Date: 12 Apr 2006 15:53:17 -0700
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
.
- Follow-Ups:
- Prev by Date: DTS Data Driven Query - Update column only if source not NULL?
- Next by Date: Re: Performing lookups with recordset variables
- Previous by thread: DTS Data Driven Query - Update column only if source not NULL?
- Next by thread: Re: source text file as input parameter from a stored procedure
- Index(es):
Relevant Pages
|
