Retrieve information from the System.Object result set in a SSIS package.

Populate a package variable with a result set using Execute SQL Task.

Write a SQL query, set the ResultSet property to Full result set and configure a package variable in the Result Set property window.The package variable’s type is System.Object.

Following code in a script task retrieves the number of records in the result set.

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
            DataTable dt = new DataTable();
            da.Fill(dt, Dts.Variables["User::JobTitles"].Value);
            MessageBox.Show(dt.Rows.ToString());

List of SSIS Error Messages

Integration Services Error and Message Reference
https://msdn.microsoft.com/en-us/library/ms345164.aspx

Error Message:
Solution file cannot be converted because it cannot be modified.To convert the solution,change the permissions
on the solution file to allow modification and re-open it.
Solution:
Make sure the sln solution file is not marked as read only.

Error Message:
[Lookup [11]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Lookup" failed because error code 0xC020901E occurred, and the error row disposition on "Lookup.Outputs[Lookup Match Output]" specifies failure on error. An error occurred on the specified object of the specified component.There may be error messages posted before this with more information about the failure.
Solution:
Change specify how to handle rows with no matching entries to Ridirect rows to no match output.The default is fail component.
Two things to be noted here, Even though values are same in both table, if the data type is different (char in 1st table and varchar in 2nd table) error will be thrown saying that no match.
In lookup properties set validate metadata to false. Because it is validating metadata as well. This should be done if you are so sure that both table structure is sync.

Error Message:
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file."
Solution:
This error was due to Format issue with source datasource text file. I did not specify correct delimiter in Bulk Insert Task component. So, do check that also

Error Message:
Validation error. Data Flow Task: Data Flow Task: There is more than one data source column with the name "1".  The data source column names must be unique.   Package1.dtsx
Solution:
Delete all data from flat file and run the ssis package.

Error Message:
SSIS Flat File Source Error: 0xC020200E Cannot open the datafile and 0xC004701A failed the pre-execute phase
Solution:
http://www.roelvanlisdonk.nl/?p=1441

Error Message:
File System Task Error: An error occurred with the following error message: "Empty file name is not legal.
Parameter name: sourceFileName".
Solution:
There is a File system task which need to pass a Dest file name or Source fie name as parameter into this task.
Now it seems it doesn't pass/input the parameter so that the program throws this Exception.

Error Message:
Failed to execute the package or element. Build errors were encountered. For more information, see the Output window.
Solution :
Close all other applications which are using current source in dataflow and try again.
If it doesn't  work right click on project and create build.It will show some errors which should be resolved.

Error Message:
Project consistency check failed. The following inconsistencies were detected: Package.dtsx has a different ProtectionLevel than the project.
Solution :
These steps got it fixed:
Open your project in Visual Studio (2012).
Right Click your project name in Solution Explorer to the right of the screen and select Properties
Change ProtectionLevel under Security to DontSaveSensitive
Double click a package and under Properties, there should be a list with properties. Change ProtectionLevel under Security to DontSaveSensitive in the Properties (do this for all packages)
Save all Close Visual Studio (2012)
Then go to C:\ProjectName\ProjectName\ And search for the file with .dtproj extension. The one with the type Integration Services Project File and not the one with the type Visual Studio Project User Options File.
Open ProjectName.dtproj with a text editor. I used notepad++, but you can also open it using Visual Studio, and other text editors.
Search for and change EncryptSensitiveWithUserKey to DontSaveSensitive.
Search for and change any number behind this to 0 zero. Search the entire file, there should be more than one if you have more packages than one.
Save the file, close it, go into Visual Studio (2012) and Right Click on the project name in Solution Explorer and click build. Everything should work now

SSIS Interview Questions & Answers

1. What is the SQL Server Import and Export Wizard?
The Import and Export Wizard is a utility that provides a simplified interface
for developing data movement operations where data is extracted from a
source and loaded into a destination, without any transformations.
Import and Export Wizard is mainly used for simple Data Movement Operations.

2.What is SSDT?
SQL Server Data Tools (SSDT) is a special edition of Visual Studio used for developing
SQL Server 2012 solutions, such as SSIS packages, SSAS multi-dimensional
models, and SSRS reports

3. What is a control flow?
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.

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

5.What is a container and how many types of containers are there?
1.Sequence Container
2.For Loop Container
3.For Each Loop Container

For Loop Container
This container executes the encapsulated tasks continously, based on an
expression.The looping continues while the result of the expression is
true.
For Each Loop Container
This container executes the encapsulated tasks continously, per each item
of the selected enumerator.for example files in a folder.
Sequence Container
This container has no programmatic logic It groups the related tasks to form a logical unit.

6.What are variables and what is variable scope?
Variables are used to store values that SSIS executables can access at runtime.This object contains a value that can be hardcoded,
dynamically set, or modified multiple times throughout the execution of the package.
Principally, variables provide a method for objects in a package to communicate with each other.

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

7.What is a breakpoint in SSIS?
Breakpoints are used to debug the SSIS Package in design time.It allows you to stop the execution of a package to investigate and troubleshoot the state of the SSIS package.
To set a breakpoint, right-click the task or container you want to observe, and select
Edit Breakpoints to open the Set Breakpoints dialog box. In the Set Breakpoints dialog box,
you can enable breakpoints on the listed conditions.

There are ten events in the Control Flow
OnPreExecute: Called when a task is about to execute.This event is raised by a task or a container immediately before it runs.
OnPostExecute: Called immediately after the execution logic of the task finishes. This event is raised by a task or container immediately after it runs.
OnError: Called by a task or container when an error occurs.
OnWarning: Called when the task is in a state that does not justify an error, but does warrant a warning.
OnInformation: Called when the task is required to provide information.
OnTaskFailed: Called by the task host when it fails.
OnProgress: Called to update progress about task execution.
OnQueryCancel: Called at any time in task processing when you can cancel execution.
OnVariableValueChanged: Called by the Integration Services runtime when the value of a variable changes. The RaiseChangeEvent of the variable must be set to true to raise this event.
OnCustomEvent: Called by tasks to raise custom task-defined events.

SSIS transformations categorized

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.