Data flow — derived column, Lookup Transformation
Control Flow — For Loop, For Each Loop, sequence container, sql server task,
Debugging — BreakPoint
Package configuration — File system and SQL SERVER configuration
Error handling — Event Logging
What are the components in the Control flow of SSIS ?
1. Bulk Insert Task
2. Execute Package Task
3. Execute Process Task
4. Expression Task
5. File system Task
6. Script Task
7. Send Mail Task
8. Web Service Task
9. FTP Task
10. Analsis Service processing Task
11. Data Flow Task
12. Execute SQL Task
13. Containers – For Loop, For Each Loop, Sequence Container.
What are the components in the Data Flow of SSIS ?
1. Source Assistant
2. Destination Assistant
4. Conditional Split
5. Data Conversion
6. Derived Column
9. Merge Join
11. OLE DB Command
12. Row Count
13. Script Component
14. Slowly Changing Dimension
16. Union ALL
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 is early and DelayValidation?
package are validated before starting the execution. This is called early validation and Package validates after started executing is called delayvalidation.
What is a Precedence Constraint and its types?
The task in the control flow can be connected using the connectors called precedence constraints OR in other words precedence constraints helps to connect various components and control the flow of execution
The condition could be either a constraint or an expression or both
Success (Task will be executed only when last task completed successfully)
Failure (Task will be executed only when last task failed)
Complete (Task will be executed no matter the last task is completed or not)
What is a container and types of container?
A container is a logical grouping of tasks. This is helpful to manage the components as a group. Any property set to the container is inherited to the components inside the group.
Types of containers in SSIS
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.
What is a variable and its scope?
A variable is used to store values.
There are two types of variables
System Variable : (ErrorCode, ErrorDescription, PackageName) User cannot change its value
User Variable : user can create and assign and read the values from the variable.
Variables have different scope depending on where it was declared.
package level variables : Available to all the tasks inside the package
container level variables : Available to those tasks that are within the container. The scope is restricted to the container level
What is a Data Viewer and its uses?
Place the Data viewer in the precedence constraint by right click and choose Data view by this we can able to view what data is flowing through that specific data path during execution.
This is used to view the flowing data at run time.
What is a SSIS breakpoint and how to configure it?
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 the transaction support feature in SSIS and how to configure it?
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 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)
How Record Set object used in SSIS ?
Record Set object is used to store the values temporary to an object variable and use that object variable in the ETL
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.