• Thanks, but i was able to use this and resolve the problem. FYI, you can follow this if anyone else is running into the same issue.

    http://agilebi.com/jwelch/2008/09/05/error-redirection-with-the-ole-db-destination/

    My issue is now resolved.