SSIS commit and Rollback

SSIS Commit Rollback

In SSIS each and every component will commit its transaction after it got executed. But if we have a scenario where the commit should take place only after a set of component execution in ETL and for this we have an option in the Connection Object – > Properties -> Set RetainSameConnection as True.

This will ensure to commit only if all the components went success belongs to the same connection object.

You can give explicit commit statement like

  1. Begin Transaction
  2. Commit Transaction
  3. Rollback Transaction -> This you can write under the ONERROR event

https://www.sqlpublic.com/wpcontent/uploads/2017/10/img_59f0cd54baa1f.png” rel=”nofollow”>https://www.sqlpublic.com/wpcontent/uploads/2017/10/img_59f0cd54baa1f.png” alt=”” />

SSIS commit and Rollback was last modified: November 15th, 2017 by Justin Antony

How Record Set object used in SSIS ?

Record Set object SSIS
Record Set object is used to store the values temporary to an object variable and use that object variable in the ETL

EG :

Get the information form flat file

Store the information in to the Object variable using the Record set object

Get the value form object variable when needed.

How Record Set object used in SSIS ? was last modified: October 1st, 2017 by Justin Antony

Refresh PowerPivot excel using SSIS

Refresh PowerPivot excel using SSIS

Install the SSIS_EXCEL_REFRESH.EXE in your ETL System and then you will find that component displaying in the SSIS Toolbox.

  1. Configure the EXCEL Refresh Tool
  2. Provide the Path where the Excel resides
  3. Set the Timeout for Refresh
  4. Run the ETL and check the Excel being refreshed.

You can check that by seeing the excel last modified date gets updated.

 

Refresh PowerPivot excel using SSIS was last modified: October 1st, 2017 by Justin Antony