Top 30 SSIS Interview Questions and Answers For Experienced Pdf Download
1. Define SSIS?
Answer: 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.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
2. Name a few SSIS components?
Answer: 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
3. What is a project and Package in SSIS?
Answer: Project is a container for developing packages. Package is nothing but an object. It implements the functionality of ETL — Extract, Transform and Load — data.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
4. What are the 4 elements (tabs) that you see on a default package designer in BIDS?
Answer: Control Flow, Data Flow, event Handler and package explorer. (Parameters – 2012 Data Tools)
For More Enroll For SQL Server Online Training.(Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
5. What is a Control flow and Data Flow elements in SSIS ?
Answer: 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.
6. What are the 3 different types of control flow elements in SSIS ?
Structures provided by Containers
Functionality provided by Tasks
Precedence constraints that connect the executables, containers, and tasks into an ordered control flow.
7. What are the 3 data flow components in SSIS ?
8. What is a breakpoint in SSIS? How is it setup? How do you disable it ?
Answer: A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an
Opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint. Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the
Object where you want to set the breakpoint and select the ‘Edit Breakpoints…’ option.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
9. What is the use of Bulk Insert Task in SSIS ?
Answer: Bulk Insert Task is used to upload large amount of data from flat files into Sql Server. It supports only OLE DB connections for destination database.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
10. What is Conditional Split transformation in SSIS ?
Answer: This is just like IF condition which checks for the given condition and based on the condition evaluation, the output will be sent to the appropriate OUTPUT path. It has ONE input and MANY outputs. Conditional Split transformation is used to send paths to different outputs based on some conditions. For example, we can organize the transform for the students in a class who have marks greater than 40 to one path and the students who score less than 40 to another path.
11. How do you eliminate quotes from being uploaded from a flat file to SQL Server ?
Answer: This can be done using TEXT QUALIFIER property. In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
12. Can you explain how to setup a checkpoint file in SSIS ?
Answer: The following items need to be configured on the properties tab for SSIS package:
Checkpoint Filename – Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it’s a good idea to use an expression that concatenates a path defined in a package variable and the package name.
Checkpoint Usage – Determines if/how checkpoints are used. Choose from these options: Never (default), If Exists, or Always. Never indicates that you are not using Checkpoints. If Exists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
Save Checkpoints – Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior
13. What are the command line tools to execute SQL Server Integration Services packages ?
Answer: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.
14. Name Transformations available in SSIS ?
Answer: 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 an 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
( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
15. What is Ignore Failure option in SSIS ?
Answer:In Ignore Failure option, the error will be ignored and the data row will be directed to continue on the next transformation. Let’s say you have some JUNK data (wrong type of data or JUNK data) flowing from source, then using this option in SSIS we can REDIRECT the junk data records to another transformation instead of FAILING the package. This helps to MOVE only valid data to destination and JUNK can be captured into separate file
16. What are containers? What are the different types of containers in SSIS ?
Answer:Containers are objects that provide structures to packages and extra functionality to tasks. There are four types of containers in SSIS, they are: For each Loop Container, For Loop Container, Sequence Container and Task Host Container
17. What are the different types of data sources available in SSIS ?
Answer:There are 7 types of data sources provided by SSIS: a.) Data Reader source b.) Excel source c.) Flat file source d.) OLEDB source e.) Raw file source f.) XML source g.) Script component.
18. What is the function of control flow tab in SSIS ?
Answer: On the control flow tab, the tasks including dataflow task, containers and precedence constraints that connect containers and tasks can be arranged and configured.
19. What is the function of Event handlers tab in SSIS ?
Answer:On the Event handlers tab, workflows can be configured to respond to package events.
For example, we can configure Work Flow when ANY task Fails or Stops or Starts.
20. What is the function of Package explorer tab in SSIS ?
Answer: This tab provides an explorer view of the package. You can see what is happening in the package. The Package is a container at the top of the hierarchy.
21. What is Solution Explorer ?
Answer:It is a place in SSIS Designer where all the projects, Data Sources, Data Source Views and other miscellaneous files can be viewed and accessed for modification.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
22. How do we convert data type in SSIS ?
Answer: The Data Conversion Transformation in SSIS converts the data type of an input column to a different data type.
( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )
23. Explain Conditional split Transformation ?
Answer: It functions as if…then…else construct. It enables send input data to a satisfied conditional branch. For example you want to split product quantity between less than 500 and greater or equal to 500. You can give the conditional a name that easily identifies its purpose. Else section will be covered in Default Output Column name.
After you configure the component, it connect to subsequent transformation/destination, when connected, it pops up dialog box to let you choose which conditional options will apply to the destination transformation/destination.
24. Explain Copy column Transformation ?
Answer: This component simply copies a column to another new column. Just like ALIAS Column in T-Sql.
25. Explain Data conversion Transformation ?
Answer: This component does conversion data type, similar to TSQL function CAST or CONVERT. If you wish to convey the data from one type to another then this is the best bet. But please make sure that you have COMPATABLE data in the column.
26. Explain Data mining query Transformation ?
Answer: This component does prediction on the data or fills gap on it. Some good scenarios uses this component is:
1. Take some input columns as number of children, domestic income, and marital income to predict whether someone owns a house or not.
2. Take prediction what a customer would buy based analysis buying pattern on their shopping cart.
3. Filling blank data or default values when customer doesn’t fill some items in the questionnaire.
27. Explain Derived column Transformation ?
Answer: Derived column creates new column or put manipulation of several columns into new column. You can directly copy existing or create a new column using more than one column also.
28. Explain Merge Transformation ?
Merge transformation merges two paths into single path. It is useful when you want to break out data into path that handles errors after the errors are handled, the data are merge back into downstream or you want to merge 2 data sources. It is similar with Union All transformation, but Merge has some restrictions:
1. Data should be in sorted order
2. Data type, data length and other Meta data attribute must be similar before merged.
29. Explain Merge Join Transformation ?
Answer: Merge Join transformation will merge output from 2 inputs and doing INNER or OUTER join on the data. But if you the data come from 1 OLEDB data source, it is better you join through SQL query rather than using Merge Join transformation. Merge Join is intended to join 2 different data source.
30. Explain Multicast Transformation ?
Answer: This transformation sends output to multiple output paths with no conditional as Conditional Split does. Takes ONE Input and makes the COPY of data and passes the same data through many outputs. In simple give one input and take many outputs of the same data.( Top 30 SSIS Interview Questions and Answers For Experienced Pdf )