Implicit conversions from SQL Server to SSIS

MSBI Interview questions & answers Part 2

1. Tell me about yourself?
2. Tell me the current project you are working? and type of your current project?
3. How much you are efficient in sql server,ssis and ssrs?
4. What is the difference between delete and truncate?
5. What is the difference between function and stored procedure?
6. What is the difference between temp table and table variable?
7. What is the difference between control flow and data flow?
8. What is merge join?
9. What are the tasks you used in data flow?
10. Types of joins?
11. What is – DML, DDL, DCL and TCL?

Notes:

• Function Cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
• A Function can be used inline in SQL Statements but stored procedure cannot.
• We can't use transactions in UDF.
• SP can have input\output parameter.Function can have only input parameter.
• We can use exception handling using Try-catch block in SP.We can't use Try-Catch block in UDF.
• Temporary tables cannot be used in User defined function.Stored procedure can use Temporary tables.
• User defined functions cannot execute Dynamic SQL.Stored procedure can execute Dynamic SQL.

• Temporary Tables are same as real tables so you can do things like CREATE INDEXes, etc We can create non – clustered index on temporary table. We cannot create a non-clustered index on table variable.If we have large amount of data and performing queries on it then temporary table may be better option than table variable since we can increase the performance of a query.Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints.
• Table variables don't participate in transactions, logging or locking.
• You can create a temp table using SELECT INTO, which can be quicker to write 
• Both table variables and temp tables are stored in tempdb.
• Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.
• We can pass the table variables as a parameter of stored procedures or functions.
• Scope of temporary table is session.Scope of table variable is batch.

• In SSIS packages, the control flow defines the tasks used in performing data management operations; it determines the order in which these tasks are executed and the conditions of their execution.

• In SSIS packages, the data flow is a special control flow task used specifically in data movement operations and data transformations.

• INNER JOIN : This join returns rows when there is at least one match in both the tables.
• OUTER JOIN : There are three different Outer Join methods.
• LEFT OUTER JOIN : This join returns all the rows from the left table with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
• RIGHT OUTER JOIN : This join returns all the rows from the right table with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
• FULL OUTER JOIN : This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.
• CROSS JOIN : This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

• DML : Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.Examples: SELECT, UPDATE, INSERT statements
• DDL : Data Definition Language. It is used to create and modify the structure of database objects in database.Examples: CREATE, ALTER, DROP statements
• DCL : Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.Examples: GRANT, REVOKE statements
• TCL : Transactional Control Language. It is used to manage different transactions occurring within a database.Examples: COMMIT, ROLLBACK statements

• The Merge Join joins two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured.The result is a table that lists all products and their country/region of origin.
• Requirements:The Merge Join Transformation requires sorted data for its inputs.The Merge Join transformation requires that the joined columns have matching metadata. For example, you cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.

MSBI Interview Questions & Answers Part 1

1. Why SSIS? What is the use of ETL?
2. What is difference between SSIS and SSAS?
3. Diference between union all and merge transformation?
4. What is difference between Multicast and Conditional Split?
5. What are the performance issues you have faced in SSIS?
6. What is a cross apply and how to use this?
7. What is CTE?Can we use more than one CTE in a single select query?
8. what is control flow and dataflow?
9. What is the difference between drilldown and drill through report?
10. How to implement type 2 SCD using SSIS and queries?

Notes : 

A drill through report allows you to go from summary to detail.

A drill down reports allows you to look to the data in different levels.

Slowly Changing Dimension in ssis support

Fixed Attribute

Select this type when the values in a column should not change.Changes are treated as errors.

Changing Attribute

Select this type when changed values should overwrite existing values.This is a Type 1 change.

Historical Attribute

Select this type when changes in column values are saved in new records.Previous values are saved in a records marked as outdated.This is a Type 2 change.

Variables,Parameters and Expressions

SSIS includes multiple objects that can be used to create dynamic packages.SSIS can dynamically set a property in a task or component using an expression, which can be built using a set of building blocks, including variables,parameters,functions, literals, and more.

Variables

Variables are used to store values that SSIS executables can access at runtime.This object contains a value that can be hardcoded, dynamically set once, or modifi ed multiple times throughout the execution of the package. Principally, variables provide a method for objects in a package to communicate with each other.

You can use variables in SSIS in the following places:

• Setting up any connection string
• Setting up row count transform in the data flow task
• Used in For Each Loop, For Loop, Parameter mapping in Execute SQL Task.
• Script task

Parameters

Parameters are a new feature introduced in SQL Server 2012.SSIS uses two types of parameters: project parameters and package parameters. Project parameters are created at the project level and can be used in all packages that are included in that project.On the other hand, package parameters are created at the package level and can be used only in that package.

Package-level parameters are extremely useful for the parent-child package design. They allow you to pass specific values between the parent packages in order to configure the child packages.When using the project deployment model, parameters are the best choices to replace package confi gurations to create a dynamic and more flexible SSIS solution.

Expressions

Expressions are used in SSIS to return a value based on the evaluation of criteria. The expression
language itself consists of identifiers, literals, functions, and operators. Some expressions can be written to assign values to variables, return the value of a calculation, or be used as conditional statements. 

Variables and parameter difference

1.Execution
The value of a parameter can’t change within the execution of a package.That means, its value remains the same for the entire execution of the package.

The value of variables can change during the execution of the package

2.Scope
Parameters can be defined at 2 different levels:
Project. These type of parameters are available to all packages within the SSIS project. 
Package. These type of parameters are available only the package on which they were defined.

Variables can be scoped at the package, container, task or event handler level.