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 to find trace or log information in sql server ?

For tracking the database for any incidents like droping any objects and event monitoring.

Starting the Trace
EXEC SP_Configure ‘default trace enabled’,1
RECONFIGURE

Stopping the Trace
EXEC SP_configure ‘default trace enabled’,1
RECONFIGURE

To find the trace information for any sql server table

select * from sys.traces where id =1

How to find trace or log information in sql server ? was last modified: October 14th, 2017 by Justin Antony

Query to search for particular column from sql database

Search particular column sql database

You can search for a particular column that present in the list of database tables in sql server.

SELECT
SCHEMA_NAME(SCHEMA_ID) + '.' + TBL.NAME AS TableName,
COL.NAME AS ColumnName
FROM SYS.TABLES AS TBL
INNER JOIN SYS.COLUMNS COL
ON TBL.OBJECT_ID = COL.OBJECT_ID
WHERE COL.NAME LIKE '%ENTER COLUMN NAME%'
ORDER BY 1

 

 

Query to search for particular column from sql database was last modified: October 10th, 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