sp_OAGetProperty help
I'm trying to call a DTS from a stored procedure. It works if I use dts.package in visual basic, but I don't want to install sql client files on every machine I want to be able to run this. So with that, can someone tell me what I'm doing wrong? Everything seems to work up until the SetProperty statement. When I use the GetProperty method, it returns null. I have verified that @inputfile contains the value I want.
Please help before I go insane.
-- CODE --
set @server = 'MyServer'
set @PkgPwd = ''
set @PkgName = 'MyPackage'
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer Failed.'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
EXEC @hr = sp_OASetProperty @oPKG,'GlobalVariables("inputfile").Value', @inputfile
IF @hr <> 0
BEGIN
PRINT '*** Set Property Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
declare @property nvarchar(254)
exec @hr = sp_OAGetProperty @oPKG,'GlobalVariables("inputfile")',@property OUT
if @hr<>0
begin
PRINT '*** Get Property Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
select @oPKG
RETURN 1
end
select @property
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
EXEC @hr = sp_OADestroy @oPKG
-- END CODE --
|
