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

What is SSIS event logging?

SSIS event logging
To log all the events that will be helpful for troubleshooting the package.

What are the different SSIS log providers?
SSIS log provider for Text files
SSIS log provider for Windows Event Log
SSIS log provider for XML files
SSIS log provider for SQL Profiler
SSIS log provider for SQL Server (Data will be stored in msdb..sysdtslog90/msdb..sysssislog table)

What is SSIS event logging? 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

Types of containers in SSIS

containers Types
Sequence Container – To group related tasks or components together
For Loop Container – To repeat a set of component execution
For Each Loop Container – Used for enumerating each object (A collection/record set/list of files in a folder). It will execute the number of times it found the collection or record or the file.

Types of containers in SSIS was last modified: October 1st, 2017 by Justin Antony

What are the components in the Data Flow of SSIS ?

components Data Flow SSIS
1. Source Assistant
2. Destination Assistant
3. Aggregate
4. Conditional Split
5. Data Conversion
6. Derived Column
7. Lookup
8. Merge
9. Merge Join
10. MultiCast
11. OLE DB Command
12. Row Count
13. Script Component
14. Slowly Changing Dimension
15. Sort
16. Union ALL
17. Audit
18. Pivot & unpivot
19. Fuzzy Grouping
20. Fuzzy Lookup
21. Copy Column
22. Export Column
23. Data Mining Query
24. CDC Splitter
25. Character Map

What are the components in the Data Flow of SSIS ? was last modified: October 1st, 2017 by Justin Antony