Execute the package from a stored procedure

• Moe M

  Old Hand

  Points: 364

  More actions

  March 9, 2006 at 8:26 am

  #365464

  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

• mike gercevich

  SSCrazy

  Points: 2961

  More actions

  March 9, 2006 at 9:03 am

  #625729

  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

• Moe M

  Old Hand

  Points: 364

  More actions

  March 9, 2006 at 12:01 pm

  #625777

  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

• mike gercevich

  SSCrazy

  Points: 2961

  More actions

  March 10, 2006 at 8:30 am

  #625998

  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

• Gonzalo Ruiz

  Old Hand

  Points: 319

  More actions

  May 31, 2007 at 11:25 am

  #710096

  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

  master_gonzalo@hotmail.com