Best practice for SSIS

Ø  Avoid copying from remote server window to local machine, SSIS package can get corrupted. Copy the SSIS Package from shared path location to local machine.

 Ø  Maintain documentation and keep it updated for Column Mapping including Data Length and Data Type for Source and Destination

 Ø  Use of handshaking table between Upstream-> Datamart (MS BI) -> Downstream applications

 
Ø  Use of SEND MAIL NOTIFICATION TASK in SSIS Package to inform about the Data loading/ cube processing start and finish to all concerned parties

 Ø  Optimize the Query/ SP run time before placing/calling it in SSIS Package to improve performance

 
Ø  Use of intermediary table in case of huge data loads will ensure faster load and unaffected performance of target table
 
Ø  Use Sequence Container with TransactionOptionproperty set as required in order to rollback a series of task if one task fails. Also, in case you want to start the failed package from point of failure, Enable Checkpoints in the package.
 
Ø  For Error handling, use OnError Event and capture Error information in System variables ErrorCode, ErrorDescription and SourceName (task name) when event fires.