calling a stored procedure

[davebat]

does anyone know how i can call a stored procedure from an activex script task within a dts package

[dptulk]

Dave,

I am assuming you are calling a SP that is in your SQLServer. I have not done this exactly but I do frequently call stored querys that are in access using the method that you described. Since querys, views, SP's are all objects I'm assuming that it would be very similar.

'This is the code I would use in the activeX of my DTS
Set TheDB = CreateObject("ADODB.Connection")
TheDB.Mode = adModeReadWrite
TheDB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="c:\somefolder\DB\MyDatabase.mdb")
 
    TheDB.Execute ("exec QueryName " & ParameterName & "") ' If there are Parameters
    TheDB.Execute ("exec QueryName") ' If there are no parameters
 
TheDB.Close
Set TheDB = Nothing

Regards,
Dave