SSIS was first introduced in SQL Server 2005, which was the next generation of SQL Server software after SQL Server 2000. SSIS is a form of ETL (Extraction, Transformation and Load) used to perform operations like loading the data performing transformations/caluculations on data to define workflow of the process flow. The SSIS engine automation perform many data maintenance tasks, so you can update data without manually firing procedures and imports.
Q: What are the tools associated with SSIS?
- Business Intelligence Development Studio (BIDS): to work with Development of SSIS Projects.
- SQL Server Management Studio (SSMS): to manage the SSIS Packages and Projects.
Q: How is SSIS different from DTS?
Older versions of SQL Server used DTS, which was similar to SSIS. DTS let you create steps that you would then assign a priority order. With SSIS, you can separate data from work flow, and SSIS has significantly better performance than older DTS packages. While performance isn’t always an issue when running jobs during off-peak hours, it’s a problem when you must run jobs during normal business hours. You can run SSIS during business hours without too much performance degradation.
Q: What is a package?
A discrete executable unit of work composed of a collection of control flow and other objects, including data sources, transformations, process sequence, and rules, errors and event handling, and data destinations.
Q: Different components in SSIS package?
- Control flow
- Data flow
- Event handler
- Package explorer
Q: What is Control Flow?
A Control Flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, we use Precedence Constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow. SQL Server Integration Services (SSIS) provides three different types of control flow elements:
- Containers that provide structures in packages
- Tasks that provide functionality
- Precedence Constraints that connect the executable, containers, and tasks into an ordered control flow.
Q: What is a Precedence Constraint and what types of Precedence Constraint are there?
SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
Types of precedence constraints and the condition could be either a constraint, an expression or both
- Success: Next task will be executed only when the last task completed successfully
- Failure: Next task will be executed only when the last task failed
- Complete: Next task will be executed no matter the last task was completed or failed.
Q: Which are the different types of Control Flow components in SSIS?
The different types of Control Flow components are:- Data Flow Tasks
- SQL Server Tasks
- Data Preparation Tasks
- Work flow Tasks
- Scripting Tasks
- Analysis Services Tasks
- Maintenance Tasks
- Containers.
Q: What is the data flow?
Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse.
Q: What are the different types of Data flow components in SSIS?
- Sources
- Transformations
- Destinations
No comments:
Write Comments