SSIS - SQL Server Integration Services
Q: What is SSIS? How it is related with SQL Server.
SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.
This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.
SQL Server Integration Services (SSIS) is a component of SQL Server which can be used to perform a wide range of Data Migration and ETL operations. SSIS is a component in MSBI process of SQL Server.
This is a platform for Integration and Workflow applications. It is known for a fast and flexible OLTP and OLAP extensions used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and multidimensional data sets.
Q: What are the tools associated with SSIS?
We use Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) to work with Development of SSIS Projects.
We use SSMS to manage the SSIS Packages and Projects.
We use Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) to work with Development of SSIS Projects.
We use SSMS to manage the SSIS Packages and Projects.
Q: What are the differences between DTS and SSIS
Data Transformation Services | SQL Server Integration Services |
Limited Error Handling | Complex and powerful Error Handling |
Message Boxes in ActiveX Scripts | Message Boxes in .NET Scripting |
No Deployment Wizard | Interactive Deployment Wizard |
Limited Set of Transformation | Good number of Transformations |
NO BI functionality | Complete BI Integration |
Q: What is a workflow in SSIS 2014 ?
Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.
Q: What is the control flow?
Workflow is a set of instructions on to specify the Program Executor on how to execute tasks and containers within SSIS Packages.
Q: What is the 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, and Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.
Q: What is the data flow?
Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.
Data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package. Data Sources, Transformations, and Data Destinations are the three important categories in the Data Flow.
Q: How does Error-Handling work in SSIS
When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.
Type of typical Errors in SSIS:
-Data Connection Errors, which occur incase the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.
-Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.
-Expression Evaluation errors, which occur if expressions that are evaluated at run time perform invalid
When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.
Type of typical Errors in SSIS:
-Data Connection Errors, which occur incase the connection manager cannot be initialized with the connection string. This applies to both Data Sources and Data Destinations along with Control Flows that use the Connection Strings.
-Data Transformation Errors, which occur while data is being transformed over a Data Pipeline from Source to Destination.
-Expression Evaluation errors, which occur if expressions that are evaluated at run time perform invalid
Q: What is environment variable in SSIS?
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
An environment variable configuration sets a package property equal to the value in an environment variable.
Environmental configurations are useful for configuring properties that are dependent on the computer that is executing the package.
Q: What are the Transformations available in SSIS?
AGGREGATE - It applies aggregate functions to Record Sets to produce new output records from aggregated values.
AUDIT - Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..
CHARACTERMAP - Performs SQL Server column level string operations such as changing data from lower case to upper case.
CONDITIONALSPLIT– Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
COPYCOLUMN - Add a copy of column to the output we can later transform the copy keeping the original for auditing.
DATACONVERSION - Converts columns data types from one to another type. It stands for Explicit Column Conversion.
DATAMININGQUERY– Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
DERIVEDCOLUMN - Create a new (computed) column from given expressions.
EXPORTCOLUMN– Used to export a Image specific column from the database to a flat file.
FUZZYGROUPING– Used for data cleansing by finding rows that are likely duplicates.
FUZZYLOOKUP - Used for Pattern Matching and Ranking based on fuzzy logic.
IMPORTCOLUMN - Reads image specific column from database onto a flat file.
LOOKUP - Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.
MERGE - Merges two sorted data sets into a single data set into a single data flow.
MERGEJOIN - Merges two data sets into a single dataset using a join junction.
MULTICAST - Sends a copy of supplied Data Source onto multiple Destinations.
ROWCOUNT - Stores the resulting row count from the data flow / transformation into a variable.
ROWSAMPLING - Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
UNIONALL - Merge multiple data sets into a single dataset.
PIVOT– Used for Normalization of data sources to reduce analomolies by converting rows into columns
UNPIVOT– Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses.
AGGREGATE - It applies aggregate functions to Record Sets to produce new output records from aggregated values.
AUDIT - Adds Package and Task level Metadata - such as Machine Name, Execution Instance, Package Name, Package ID, etc..
CHARACTERMAP - Performs SQL Server column level string operations such as changing data from lower case to upper case.
CONDITIONALSPLIT– Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
COPYCOLUMN - Add a copy of column to the output we can later transform the copy keeping the original for auditing.
DATACONVERSION - Converts columns data types from one to another type. It stands for Explicit Column Conversion.
DATAMININGQUERY– Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
DERIVEDCOLUMN - Create a new (computed) column from given expressions.
EXPORTCOLUMN– Used to export a Image specific column from the database to a flat file.
FUZZYGROUPING– Used for data cleansing by finding rows that are likely duplicates.
FUZZYLOOKUP - Used for Pattern Matching and Ranking based on fuzzy logic.
IMPORTCOLUMN - Reads image specific column from database onto a flat file.
LOOKUP - Performs the lookup (searching) of a given reference object set against a data source. It is used for exact matches only.
MERGE - Merges two sorted data sets into a single data set into a single data flow.
MERGEJOIN - Merges two data sets into a single dataset using a join junction.
MULTICAST - Sends a copy of supplied Data Source onto multiple Destinations.
ROWCOUNT - Stores the resulting row count from the data flow / transformation into a variable.
ROWSAMPLING - Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
UNIONALL - Merge multiple data sets into a single dataset.
PIVOT– Used for Normalization of data sources to reduce analomolies by converting rows into columns
UNPIVOT– Used for denormalizing the data structure by converts columns into rows incase of building Data Warehouses.
Q: How to log SSIS Executions?
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages. This is not enabled by default. Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.
Q: How do you deploy SSIS packages?
SSIS Project BUILD provides a Deployment Manifest File. We need to run the manifest file and decide whether to deploy this onto File System or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQL Server.
SSIS Project BUILD provides a Deployment Manifest File. We need to run the manifest file and decide whether to deploy this onto File System or onto SQL Server [ msdb]. SQL Server Deployment is very faster and more secure then File System Deployment. Alternatively, we can also import the package from SSMS from File System or SQL Server.
Q: What are variables and what is variable scope?
Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.
Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions. Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.
Q:Can you name five of the Perfmon counters for SSIS and the value they provide?
- SQLServer:SSIS Service
- SSIS Package Instances
- SQLServer:SSIS Pipeline
- BLOB bytes read
- BLOB bytes written
- BLOB files in use
- Buffer memory
- Buffers in use
- Buffers spooled
- Flat buffer memory
- Flat buffers in use
- Private buffer memory
- Private buffers in use
- Rows read
- Rows written
Q. Define SSIS?
Ans:
SQL Server Integration Services — commonly known as SSIS is the new platform that was introduced in SQL Server 2005, for data transformation and data integration solutions. This replaced the DTS in SQL Server 2000.
Q. Name a few SSIS components?
Ans:
- Integration Services Projects
- Integration Services Packages
- Control Flow Elements
- Data Flow Elements
- Integration Services Connections
- Integration Services Variables
- Integration Services Event Handlers
- Integration Services Log Providers
Q. What is a project and Package in SSIS?
Ans:
Project is a container for developing packages. Package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.
Q. What are the 4 elements (tabs) that you see on a default package designer in BIDS?
Ans:
Control Flow, Data Flow, event Handler and package explorer. (Parameters – 2012 Data Tools)
Q. What is a Control flow and Data Flow elements in SSIS?
Ans:
Control Flow:
Control flow element is one that performs any function or provides structure or control the flow of the elements. There must be at least one control flow element in the SSIS package. In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.
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
Data Flow:
All ETL tasks related to data are done by data flow elements. It is not necessary to have a data flow element in the SSIS package. A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.
Q. What are the 3 different types of control flow elements in SSIS?
Ans:
- Structures provided by Containers
- Functionality provided by Tasks
- Precedence constraints that connect the executables, containers, and tasks into an ordered control flow.
Q. What are the 3 data flow components in SSIS?
Ans:
- Source
- Transformation
- Destination
Q. What are connections and connection managers in SSIS?
Ans:
Connection as its name suggests is a component to connect to any source or destination from SSIS — like a sql server or flat file or lot of other options that SSIS provides. Connection manager is a logical representation of a connection.
Q. What is the use of Check Points in SSIS?
Ans:
SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.
Q. What are the command line tools to execute SQL Server Integration Services packages?
Ans:
DTSEXECUI – When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.
DTEXEC – This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.
Q. Can you explain the SQL Server Integration Services functionality in Management Studio?
Ans:
You have the ability to do the following:
- Login to the SQL Server Integration Services instance
- View the SSIS log
- View the packages that are currently running on that instance
- Browse the packages stored in MSDB or the file system
- Import or export packages
- Delete packages
- Run packages
Q. Can you name some of the core SSIS components in the Business Intelligence Development Studio you work with on a regular basis when building an SSIS package?
Ans:
- Connection Managers
- Control Flow
- Data Flow
- Event Handlers
- Variables window
- Toolbox window
- Output window
- Logging
- Package Configurations
Q. Name Transformations available in SSIS?
Ans:
DATACONVERSION: Converts columns data types from one to another type. It stands for Explicit Column Conversion.
DATAMININGQUERY: Used to perform data mining query against analysis services and manage Predictions Graphs and Controls.
DERIVEDCOLUMN: Create a new (computed) column from given expressions.
EXPORTCOLUMN: Used to export a Image specific column from the database to a flat file.
FUZZYGROUPING: Used for data cleansing by finding rows that are likely duplicates.
FUZZYLOOKUP: Used for Pattern Matching and Ranking based on fuzzy logic.
AGGREGATE: It applies aggregate functions to Record Sets to produce new output records from aggregated values.
AUDIT: Adds Package and Task level Metadata: such as Machine Name, Execution Instance, Package Name, Package ID, etc..
CHARACTERMAP: Performs SQL Server column level string operations such as changing data from lower case to upper case.
MULTICAST: Sends a copy of supplied Data Source onto multiple Destinations.
CONDITIONALSPLIT: Separates available input into separate output pipelines based on Boolean Expressions configured for each output.
COPYCOLUMN: Add a copy of column to the output we can later transform the copy keeping the original for auditing.
IMPORTCOLUMN: Reads image specific column from database onto a flat file.
LOOKUP: Performs the lookup (searching) of a given reference object set to a data source. It is used for exact matches only.
MERGE: Merges two sorted data sets into a single data set into a single data flow.
MERGEJOIN: Merges two data sets into a single dataset using a join junction.
ROWCOUNT: Stores the resulting row count from the data flow / transformation into a variable.
ROWSAMPLING: Captures sample data by using a row count of the total rows in dataflow specified by rows or percentage.
UNIONALL: Merge multiple data sets into a single dataset.
PIVOT: Used for Normalization of data sources to reduce anomalies by converting rows into columns
UNPIVOT: Used for de-normalizing the data structure by converts columns into rows in case of building Data Warehouses.
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.