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

Initialization of data source failed – When Opening PowerPivot Excel

data source Initialization failed
This error occurs due to Excel Version Compatibility. When a New Data Model is opened in an unsupported old Excel Version then it will throw out the “Initialization of data source failed” error.

Initialization of data source failed – When Opening PowerPivot Excel was last modified: October 6th, 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

What is a SSIS breakpoint and how to configure it?

SSIS breakpoint
To troubleshoot an SSIS Package we use the breakpoints.
Right click on any task in control flow and then select Edit Breakpoint, This will display a Breakpoint window. choose when the execution to be paused(OnPreExecute, OnPostExecute, OnError events). This will be helpful to see the values of a variable at runtime using the output window.

What is a SSIS breakpoint and how to configure it? was last modified: October 1st, 2017 by Justin Antony