DTS return status by sp_OAMethod

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS return status by sp_OAMethod

Author

Topic

inonli
Starting Member

6 Posts

Posted - 2003-07-16 : 13:00:42

Hello,
I need to know if have error when return from DTS called by SP.
I write exec @rc = sp_OAMethod @objPackage, 'Execute' in this case @rc get the 0 value all the time even if the DTS not run correctly. if i add after 'Execute' another parameter with OUTPUT like saw in BOL i get all the time error even if the DTS run ok...
i cant achieve the right syntax to know if have error when have and if not when havent. I must know it in the file level like integer overflow or so like DTSRUN gives me but cant use it as dont have \A option in ver 7.0. is there problem for that. i tried to set the package with the object and call to DTSRUN but sure it doesnt recognize the global variables...

Thank you
Inonli

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-16 : 14:53:55

Well, I think that in your 2nd case you get just a syntax error...
As to the 1st case, I'll check it tomorrow... Maybe you should
check 'Fail package on first error' checkbox on 'General' tab of
Package Properties or turn on 'Write completion status to Error Log'.

- Vit

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-16 : 18:21:30

The return code will just show you whether the dts package completed successfully - which it has even if a step failed.
To find error info from the package you could step through the steps and look at the error info for each step.

You can also set the package to report a failure
Set the package property "fail package on first error" this will report a package failure.
Or set the step workflow propertty "Fail package on step failure".

They will both give you an @rc from exec @rc = sp_OAMethod @objPackage, 'Execute'

Hope they are available in v7.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

inonli
Starting Member

6 Posts

Posted - 2003-07-17 : 02:09:40

The package realy fail if say i dont put the file in the directory or make some overflow in data but i cant see it in @rc...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-17 : 07:27:34

Try to get some use of following:

1) add into your package 'ActiveX Script Task' as the very last task of
your package;
2) add Workflow 'On Success' from the last but one task to this Script Task;
3) in this Script assign new value (e.g. "OK!!") to some new global variable
which starting value was e.g. "Error!!":

Function Main()
DTSGlobalVariables("asd").Value="OK!!"
Main = DTSTaskExecResult_Success
End Function

Now in your OA code you can check its value:

declare @hr int, @ob int, @j varchar(50)
exec @hr=sp_OACreate 'DTS.Package', @ob output
print @hr
exec @hr=sp_OAMethod @ob,
'LoadFromSQLServer("myServer","sa","",0,,,,"myPackage")'
print @hr
exec @hr=sp_OAMethod @ob, 'Execute'
print @hr
exec @hr=sp_OAGetProperty @ob, 'GlobalVariables("asd").Value', @j output
print @j
print @hr
exec @hr=sp_OAMethod @ob, 'Uninitialize'
print @hr
exec @hr=sp_OADestroy @ob
print @hr

- Vit

Edited by - Stoad on 07/17/2003 07:34:06

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-17 : 07:43:52

Anyway in pure VBS (VBA) this trick works perfectly.
I mean we can retrieve new value of a Global Variable
after the package executed.

- Vit

inonli
Starting Member

6 Posts

Posted - 2003-07-17 : 10:43:36

Yhank you very much.
A very useful trick to know.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-17 : 13:20:50

quote:

The package realy fail if say i dont put the file in the directory or make some overflow in data but i cant see it in @rc...

Does the package fail or does the step fail?
The package usually doesn't fail onless one on the options I indicated above is set (in 2000).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-17 : 16:37:15

Of course 'Fail package on first error' helps pretty well (ver. 7.0 I mean)...
To be honest I not much understand why inonli ignores this fact.

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources