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

Types of measures/facts tables

Fact Table Types

Fully Additive Facts: This measure which can be added across all the associated dimensions.
Example: sales amount is a fact which can be summed across different dimensions like customer, geography, date, etc.

Semi-Additive Facts: These are facts which can be added across only few dimensions.
Example: bank balance is a fact which can be summed across the customer dimension but it cannot be added across the date dimension

Non-Additive Facts: These are facts which cannot be added across any of the dimensions in the cube.
Example: profit margin is a fact which cannot be added across any of the dimensions. we can not add against product dimension.

Derived Facts: Derived facts are the facts which are calculated from one or more facts,
For example: profit margin.Which is calculated when needed

Factless Facts: Fact table will not have a measure they will have only the key values(Foreign key) refered to the dimension are called factless fact table.

Types of measures/facts tables was last modified: October 1st, 2017 by Justin Antony

How to create a simple report ?

simple SSRS report
New -> Project -> Report Server Project -> Right click on Shared DataSet -> Add New Data Source
Right click on Report Folder -> Add New Item
In the Report Data Tool Bar -> Right Click on the Data Sources -> Select the shared Data Source which we created already
Right Click on the Data Set -> Add DataSet -> Define your query here
Drag and Drop a table
Pick the Data fields from dataset and place it on the table.
Align and format the Table Accordingly
Add header to your Report.
Save and Run the Report.

How to create a simple report ? was last modified: October 1st, 2017 by Justin Antony

What is the transaction support feature in SSIS and how to configure it?

transaction support feature SSIS
If a package fails in the middle then SSIS used this functionality to start the execution from the place where it failed. Also the transactions gets roll-backed aswell if needed.
Supported – Parent object has initiated a transaction and child package will participate in it this transaction
Required – This will create and start a new transaction
NotSupported – This will not support the transaction
We need to enable MS DTC (Microsoft Distributed Transaction Coordinator) Windows Service for transaction support

What is the transaction support feature in SSIS and how to configure it? 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