sql server dts: DTS / COM puzzle -- sql server dts

DTS / COM puzzle

earthStrapped
9/25/2007 8:08:01 AM

sql server dts:

I am having a puzzling problem with a DTS package in SQL 2000 that uses a
combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue
occurs with one of (the second) the ActiveX Script Task. The script invokes
a COM object that was written in C#. This COM object connects to the
database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as
part of the package, the record set is being written but the stored procedure
is not invoked (SQL Profile confirms this).

I haven't really begun a serious attempt to troubleshoot the problem, though
I have played around with the Transactions and OLE DB properties of the DTS
Package.

Any suggestions as to why the behavior might be different, or some
straightforward tips on troubleshooting would be appreciated. I have access
to the COM component source.

Re: DTS / COM puzzle

West End Computer Solutions, LLC
10/28/2007 12:14:32 AM

Have you tried moving the stored procedure call to a DTS SQL Task, and
putting 'EXEC <stored proc> in it ? It would become the step after the
ActiveX Script.

[quoted text, click to view]