Hi,

I am trying to execute a dts package from a stored procedure. I am
using the sp_OAxxx stored procedures to create, load and execute the
dts package. When I run the stored procedure, no errors occur but the
dts package is not executed. I tried with Windows authentication and
sql Authentication as well. In both cases the result is same ( the
package is not executed).
the dts package runs fine when I run it from Enterprise manager. Is
there something I am doing wrong here? Has anyone come across this
situation before?

Following is the code I am using.
DECLARE @oPKG INT,
@HResult INT,
@ServerParams varchar(100)
SET @ServerParams = 'LoadFromSQLServer(' + @@ServerName +
',,,256,,,,TestPkg)'
-- SET @ServerParams = 'LoadFromSQLServer(' + @@ServerName +
',uid,pwd,0,,,,TestPkg)'

EXEC @HResult = sp_OACreate 'DTS.PACKAGE', @oPKG OUT
IF @HResult = 0
BEGIN
EXEC @HResult = sp_OAMethod @oPKG, @ServerParams, NULL
IF @HResult = 0
BEGIN
EXEC @HResult = sp_OAMethod @oPKG, 'Execute' --Execute the DTS Package
IF @HResult = 0
BEGIN
EXEC @HResult = sp_OADestroy @oPKG --
END
END
END

Thanks in advance
Dan

Did you take a look at

Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)
--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Dan" <dthullimelli (AT) yahoo (DOT) com> wrote