[dba-SQLServer] Check for an error in a script

Does anyone know how to test to see if an insert fails in a DTS package?  I
am trying to create a script that will be part of a DTS package that runs
nightly, importing data from a text file into a holding area, and then the
script will move the data from the holding area into the production tables.
What I am trying to do is if the record won't insert because of some
violation, a failure flag is flipped for that record to signify a need to be
reviewed by someone.  If the insert is successful, then the record is
removed from the holding table.  

 

Any help is appreciated

 

Thanks

 

Ken