General discussion


DTS ActiveX calling Excel Macro

By kaypaul ·
I have a DTS package ,which used ActiveX to call Excel application macro to update a few worksheets.

Each time it execute in the SQL Server Agent, it seem to go on forever. Usually takes around 5 to 7 mins. But, if I execute manually sometime it completed successfully and recently giving me a message like 'Switch' or 'Retry' because process busy.

How can I debug such and error?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by sameer.premji In reply to DTS ActiveX calling Excel ...

First things first...

1. Make sure you have applied latest OS patches on the server (after you have tested it elsewhere).

2. Make sure you have applied latest SQL Server patches (upto SP4).

3. After you have done the above steps, open your package and right-click on the task icon that contains the ActiveX script. Select Workflow, then Workflow Properties, click on Options tab, make sure that 'Execute on main package thread' is checked and all the rest of the items are UNchecked.

4. If that doesn't help, it could be a slower network at peak times or simply your server needs fine tuning to serve I don't know how many users.

I would talk to the Network Admin as well as check every aspect of the database - memory, backup strategy in place, scheduled jobs that might be in conflict, etc.

Good Luck,


Collapse -

by kaypaul In reply to

Poster rated this answer.

Related Discussions

Related Forums