DTS - VB Script - Stored Procedure

[ambs24]

Can someone provide me with an example of a DTS package that calls a Stored Procedure from within the VB Script of a transformation?

MORE DETAILS

We have a COM object that parses out a name. We can call this object from a stored procedure - passing in the full name. The stored procedure then calls the COM object and returns the individual parts of the name as separate variables.

I NEED EITHER

(1) An example of VB Script that is executed during a DTS transformation which passes a variable (a field from the table that is being imported) to an external COM object, and then posts several new variables to the destination data source.

OR

(2) An example of VB Script that is executed during a DTS transformation which passes a variable (a field from the table that is being imported) to a stored procedure, and then posts several new variables to the destination data source. (In this case, I would also like an example of the stored procedure to make sure I am sending/receiving values correctly).

OR

(3) An example of an Execute SQL Task (DTS) that uses either a direct COM object call or a Stored Procedure to manage this task AFTER the initial data import has been completed.


ALSO

It would be helpful if you told me what you think the best practice would be. I would like to do #1, above, because this would complete the transformation at the same time that the file is imported - but perhaps this would actually run slower than #3?

 

[nigelrivett]

You can call the com object from an SP which means that you can already do (3).

Can you re-code the com object functionality in t-sql and put it in an SP? This would probably be simplest but you might be losing re-use.

It's usually better to import data without processing then process from a staging table. This makes it simpler to maintain as it's easy to spot where the problem is.

======================================
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.