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
Set TheDB = Nothing