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