Error running DTS from sp_OAMethod
-
Hi,
I'm trying to run a DTS package fro a stored procedure.
I did find a complete article and example from the site http://www.swynk.com/friends/green/dtsole.asp.
My problem is that after calling the method LoadFromSqlServer with sp_OAMethod, i get the error "The system cannot find the file specified." (0x80030002) from the source " Microsoft Data Transformation Services (DTS) Package"
Anyone have an idea ???
Thanks.
Gianangelo
-
GianAngelo
I am using sql2000 server.
I do not know what you do exactly but I can show what I have done so you may find the answer:
I am using the sp to call DTS package with passing global variable if needed.
Cheers
Tom
Calling sp:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.CallAnyPackage
@PackageName varchar(128),
@gvInfo varchar(511) = NULL,
@prntResults Char(3) = 'NOT',
@strOutput Varchar(2047) OUTPUT
AS
DECLARE @strReturn varchar(1023)
DECLARE @Ret Integer
DECLARE @chrAssignChar varchar(2)
DECLARE @chrSearchFor varchar(5)
SET @chrAssignChar = '='
SET @chrSearchFor = '~'
SET @strReturn = ''
SET @Ret = 0
EXEC @Ret=spExecutePKGAllGlobalVariables
'servername',
@PackageName, -- Package Name (Defaults to most recent version)
'username', -- User Name if using SQL Security to load Package
'password',-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
0,-- 0 = SQL Server Security, 1 = Integrated Security
'',-- Package Password
@strReturn OUTPUT,-- Return string
@chrAssignChar,-- separator between the name and value in gvString
@chrSearchFor, -- separator between gvStrings
@gvInfo -- Optional Global Variables String ([GlobalVariableName=GlobalVariableValue~..])
-- 'gv1=1~gv2=20020411~gv3=Needle'
IF @prntResults = 'YES'
PRINT @strReturn
Set @strOutput = @strReturn
RETURN @Ret
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------
Code for: spExecutePKGAllGlobalVariables follows
-----------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROC spExecutePKGAllGlobalVariables
@server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@UserName varchar(255) = Null, -- User Name if using SQL Security to load Package
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = '',-- Package Password
@strReturn varchar(1023) OUTPUT,-- Return string
@chrAssignChar varchar(2) = '=',-- separator between the name and value in gvString
@chrSearchFor varchar(5) = '~', -- separator between gvStrings
@GlobalVString varchar(511) = NULL -- Optional Global Variables String ([GlobalVariableName=GlobalVariableValue~..])
-- 'gv1=1~gv2=20020411~gv3=Needle'
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
DECLARE @GVName varchar(255), @GVValue varchar(255)
Declare @intPos INTEGER
Declare @gvString varchar(511)
Declare @strSearchIn varchar(511)
Declare @nCnt Integer
Declare @strTemp varchar(1023)
Declare @intRet Integer
DECLARE @CRLF char(2)
SET @CRLF = char(13) + char(10)
SET @intRet = 0
SET @strReturn = ''
SET @strTemp = ''
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
-- PRINT '*** Create Package object failed'
SET @strReturn = @strReturn + '*** Create Package object failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
SET @intRet = 1
RETURN @intRet
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
BEGIN
IF @UserName IS NOT NULL
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + @UserName + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
END
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
-- PRINT '*** LoadFromSQLServer failed'
SET @strReturn = @strReturn + '*** LoadFromSQLServer failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
RETURN 1
END
IF @GlobalVString IS NOT NULL
BEGIN
SET @strSearchIn = @GlobalVString -- 'gv1=1~gv2=20020411~gv3=Needle'
Set @chrSearchFor = '~'
Set @intPos = 1
Set @nCnt = 0
WHILE (@intPos > 0)
BEGIN
SET @intPos = CHARINDEX(@chrSearchFor, @strSearchIn)
If @intPos > 0
BEGIN
SET @nCnt = @nCnt + 1
SET @gvString = LEFT(@strSearchIn, @intPos-1)
SET @strSearchIn = Right(@strSearchIn, Len(@strSearchIn)- @intPos)
SET @GVName = LEFT(@gvString, CHARINDEX('=', @gvString)-1)
SET @GVValue = RIGHT(@gvString, LEN(@gvString) - CHARINDEX('=', @gvString))
--PRINT 'gv ' + @GVNAME + ': (' + @GVVAlue + ') '
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
--PRINT '*** GlobalVariable ' + @GVName + ' assignment with ' + @GVValue + ' Failed'
SET @strReturn = @strReturn + '*** GlobalVariable ' + @GVName + ' assignment with ' + @GVValue + ' Failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
RETURN 1
END
END
ELSE
BEGIN
SET @gvString = @strSearchIn
SET @GVName = LEFT(@gvString, CHARINDEX('=', @gvString)-1)
SET @GVValue = RIGHT(@gvString, LEN(@gvString) - CHARINDEX('=', @gvString))
--PRINT 'gv ' + @GVNAME + ': (' + @GVVAlue + ') '
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
-- PRINT '*** GlobalVariable ' + @GVName + ' assignment with ' + @GVValue + ' Failed'
SET @strReturn = @strReturn + '*** GlobalVariable ' + @GVName + ' assignment with ' + @GVValue + ' Failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
RETURN 1
END
END
END
/*
IF @GlobalV_1 IS NOT NULL
BEGIN
SET @GVName = LEFT(@GlobalV_1, CHARINDEX('=', @GlobalV_1)-1)
SET @GVValue = RIGHT(@GlobalV_1, LEN(@GlobalV_1) - CHARINDEX('=', @GlobalV_1))
SET @Cmd = 'GlobalVariables("' + @GVName + '").Value'
EXEC @hr = sp_OASetProperty @oPKG, @Cmd, @GVValue
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable 1 Assignment Failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
END
*/
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
-- PRINT '*** Execute failed'
SET @strReturn = @strReturn + '*** Execute failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
SET @intRet = 9
-- RETURN @intRet
END
-- Check Pkg Errors
SET @strTemp = ''
EXEC @ret=spDTSDisplayPkgErrors @oPKG, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
-- PRINT '*** UnInitialize failed'
SET @strReturn = @strReturn + '*** UnInitialize failed' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
IF @intRet = 9
RETURN @intRet
ELSE
BEGIN
SET @intRet = 1
RETURN @intRet
END
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
SET @strReturn = @strReturn + '*** sp_OADestroy returned error !' + @CRLF
EXEC sp_DTSDisplayOAErrorInfo @oPKG, @hr, @strTemp OUTPUT
SET @strReturn = @strReturn + @strTemp
IF @intRet = 9
RETURN @intRet
ELSE
BEGIN
SET @intRet = 1
RETURN @intRet
END
END
ELSE
BEGIN
IF @intRet = 9
BEGIN
SET @strReturn = @strReturn + '*** RUN finished with Error ! ' + CONVERT(varchar(12), @intRet) + @CRLF
RETURN @intRet
END
ELSE
BEGIN
SET @strReturn = @strReturn + '*** RUN was OK !' + @CRLF
SET @intRet = 0
RETURN @intRet
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Ok guys,
the problem was a different service pack between the "client" where i connect ad run the s.p. and the "server" where the package is based
.I workaround this problem by putting a password on the dts package and, when i load it, i provide this password.
Tom (ipTomN), i will use your example, thanks.
Thank you to all
Ciao
.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply