Setting Global Var inside pkg from SProc 1

[dkern]

I have a global variable (ImportFilename) that acts as the Datasource (ASCII filename) that is used in a dynamic properties task. It works fine when executed from within the pkg or even externally when the default property setting is used. I have tried many iterations to get the syntax correct in passing a new value to it from the call but can't seem to get it.... cannot find any Books Online or programming reference example....

Code flow:
exec usa_runcreditvoiddtspkg -- starts the process

<inside procedure>
... initial lines of code including the LoadFromSQLServer
...
declare @filename as varchar (100)
set @Filename= '\\svrname\c\inetpub\

http://wwwroot\usadev\imports\credit1.txt'

EXEC @hr = sp_OAMethod @oPKG, 'Execute',null, @ImportFilename=@Filename

Returns the error:
OLE Automation Error Information
HRESULT: 0x80020006
Source: ODSOLE Extended Procedure
Description: Unknown name.

Another try:
declare @filename as varchar (100)
set @Filename= '\\svrname\c\inetpub\

http://wwwroot\usadev\imports\credit1.txt'

EXEC @hr = sp_OAMethod @oPKG, 'Execute',null, @Filename

Returns the error:
OLE Automation Error Information
HRESULT: 0x80020011
Source: ODSOLE Extended Procedure
Description: Does not support a collection

Thanks in advance!





...
...

 

[bs26]

Hi there,
you nee to use sp_OASetProperty to set the global vraiables, before you call the excute method

EXEC @hr = sp_OASetProperty @oPKG, <Global variable name>, <value>

ex.
EXEC @hr = sp_OASetProperty @oPKG, 'Filename', '\\svrname\c\inetpub\

http://wwwroot\usadev\imports\credit1.txt'

/b

 

[dkern]

Thanks for the tip.....I have tried it but it does not seem to be setting the value properly. In fact when I use the sp_OAGetProperty (pre and post my sp_oasetproperty) on the property it returns null. The pkg runs successfully using the defaulted value of the variable in the pkg.

Perhaps it is a problem with the pkg itself or my loading of it, but here is the code and I appreciate any help you can offer


--Creating the DTS Package Object:
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Loading the Package:
EXEC @hr = sp_OAMethod @oPKG,'LoadFromSQLServer(&quot;WEBDEV1&quot;, &quot;user&quot;, &quot;pswrd&quot;, 0, , , , &quot;importcreditvoids&quot', NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

----------------------------
----------------------------
-- test ONLY
declare @filename varchar(120)
set @Filename= '\\svr\c\inetpub\

http://wwwroot\imports\credit1.txt'

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

-- test to see the property before
DECLARE @property varchar(255)
EXEC @hr = sp_OAGetProperty @oPKG, 'ImportFilename', @property OUT
select @property -- RETURNS NULL

-- use the passed filename to set the global variable inside the pkg
EXEC @hr = sp_OASetProperty @oPKG, 'ImportFilename', @Filename

-- test to see the property after
EXEC @hr = sp_OAGetProperty @oPKG, 'ImportFilename', @property OUT
select @property -- RETURNS NULL

--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute' -- uses the default improtfilename inthe pkg

Thanks!
Dave

 

[bs26]

Hi Dave,

REPLACE

EXEC @hr = sp_OASetProperty @oPKG, 'ImportFilename', @Filename

WITH

EXEC @hr = sp_OASetProperty @oPKG, GlobalVariables(&quot;ImportFilename&quot.Value, @Filename

Let me know how it turned out
/b

 

[dkern]

Many Thanks.... the final syntax was modified just a little to put the entire 2nd parm in single quotes to force as a string....

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables(&quot;ImportFilename&quot.Value', @Filename

Thanks again for your efforts!