Checking if a stored proc executed successfully from an ASP

[Deam]

From an ASP, I want to check if the stored procedure executed successfully or not.(the sp executes a
dts package and inserts data into different tables)
This is a part of the asp...it pops up the success msg box even when the dts package fails.Do I have
to make the sp return a value or something?????????to capture that in the asp??????????????
Am i missing something here ??Help please...

function ExecSP()
{
If (&quot;<%=sFileExists%>&quot;==&quot;NO&quot
{
alert(&quot;The file does not exist at the specified location&quot;
}
else
{ // Build Stored Procedure Execute Statement
var rsMyData = rdsGetRDCRecordSet(rdcData);
var sSQL = &quot;EXECUTE sp_Import'&quot;;
sSQL += txtUserID.value + &quot;','&quot;;
sSQL += Password.value + &quot;','&quot;;
if (rdsSetRecordSource(rdcWrite, sSQL,0,1))
{
var rsWork = rdsGetRDCRecordSet(rdcWrite);
if (!rsWork)
{
appMsgBox(&quot;<%=interpretPageToken(&quot;MsgPass&quot%>&quot;
document.body.style.cursor = &quot;&quot;;
}
else
{
appMsgBox(&quot;<%=interpretPageToken(&quot;MsgFailure&quot%>&quot;
document.body.style.cursor = &quot;&quot;;
}
}
}
}