March 9, 2006 at 8:26 am
Hey guys,
I try to execute a package from a stored procedure using the following steps:
DECLARE
@oPKG int
DECLARE
@hr int
EXEC
@hr = sp_OACreate 'DTS.Package', @oPKG OUT
EXEC
@hr = sp_OAMethod @oPKG,'LoadFromSQLServer("foo", "", "", 256, , , , "foo1")',NULL
EXEC
@hr = sp_OAMethod @oPKG, 'Execute'
EXEC
@hr = sp_OADestroy @oPKG
I don't receive any error messages but no package actually gets executed and no output is generated.
Even when I change the package name it gives me an error message that "Package could not be loaded" so it shows that it can find and load the package.
Any heads up.
Thanks
Moe M
Database Consultant
http://www.cubeangle.com
March 9, 2006 at 9:03 am
I'm pretty sure that this is not the correct syntax for the sp_OAMethod call to load the package:
EXEC @hr = sp_OAMethod @oPKG,'LoadFromSQLServer("foo", "", "", 256, , , , "foo1")',NULL
I think you want something more on the lines of:
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer',NULL, @ServerName=@svr, @ServerUserName=@login, @PackageName=@packagename, @Flags=@flag, @PackagePassword=@pkgPwd, @ServerPassword=@userpwd
-Mike Gercevich
March 9, 2006 at 12:01 pm
Hey Mike,
Thanks for the comment. Actually I used both syntax but still no error message and no output.
Ryan
Moe M
Database Consultant
http://www.cubeangle.com
March 10, 2006 at 8:30 am
Try PRINT STR(@HR) after each line as well as running a trace under profiler. That should give you a better understanding of what is going on from the servers point of view.
-Mike Gercevich
May 31, 2007 at 11:25 am
Hi
This code works very well but how can I run just one step of the package ?
I have one DTS package with several steps (step(1), step(2), step(n),)
Sometimes I wish to execute the whole package but others I need just one task of them.
I found that I can Instantiate the task with a command like
EXEC sp_OAGetProperty @objdts, 'Tasks(1)', @TaskHandle OUTPUT
But I cannot call 'execute' method for that task
EXEC @ResultCode = sp_OAMethod @TaskHandle, 'Execute'
It fails
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply