Setp by step creating a first package in SSIS

 1. Go to start button click on Microsoft Sql server 2008R2. You will find SQL sever Business Intelligence development studio. Click on that.

2.       You will get the following screen

3.  Click on new project you will get the following screen

4.For creating the new SSIS project follow the following steps

A.      After clicking the new project menu you get the above screen.

B.       Select Integration Services Project.

C.      You can change the project name.

D.      You can write the path where you want to keep your project.

E.       Either you can browse the location where you want to keep your project.

F.       Solution Name: - you can change the Solution Name.

G.     Click ok.

5. You will get the following screen.

Here I will explain basic component

A.      Solution Explorer: - SSIS Package Explore is Visual Studio 2008 add in for SSIS Packages. You can manage your SSIS Packages like Solution Explorer; you can open folder and sub folders and put your packages into them. By default Solution explorer having four folders.

Ø  Data Source: - A data source represents a connection that can be shared among multiple packages in a project. You can create a connection manager in a package from an existing data source

Ø  Data Source view: - A data source view represents a subset of the data in a data source, and can also contain named queries. Data source views can be shared by multiple packages in a project. You can designate tables, views, or named queries from a data source view as the source of data for a data flow source.

Ø  SSIS Package: - A package represents an organized collection of connections, control flow elements, data flow elements, and other objects. The package is the unit of work in Integration Services that provides and supports extraction, transformation, and loading (ETL) functionality. A single project can contain multiple packages. 

Ø  Miscellaneous folder: - If you add files of other types to your Integration Services project, such as documents or images, these files are grouped in the miscellaneous folder.

B.      Properties: - The Properties window provides a categorized and alphabetical list of properties. To arrange the Properties window by category, click the Categorized icon.

C.      Toolbox: - In the SSIS Toolbox, control flow and data flow components are organized into categories. You can expand and collapse categories for viewing and you can change the organization of components according to your preferences. You can restore the default organization, by right-clicking inside the toolbox and then click Restore Toolbox Defaults.

The Favorites and Common categories appear in the toolbox when you select the Control Flow, Data Flow, and Event Handlers tabs. The Other Tasks category appears in the toolbox when you select the Control Flow tab or the Event Handlers tab. The Other Transforms, Other Sources, and Other Destinations categories appear in the toolbox when you select the Data Flow tab.

D.      Control flow: - A package consists of a control flow and, optionally, one or more data flows. SQL Server Integration Services 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. We will discuss details in next post.

E.       Data flow: - SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets. We will discuss details in next post.

F.       Event handler: - At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events. For example, an OnError event is raised when an error occurs. You can create custom event handlers for these events to extend package functionality and make packages easier to manage at run time. We will discuss details in next post.

G.     Package Explorer: - With the help of package explorer you see a hierarchical view of all of the elements in the package like: configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables. If a package contains a Data Flow task etc.

6. Here I am going to create my first SSIS task (Execute SQL Task).

Drag an Execute SQL Task from toolbox from control flow item.

Double click on execute SQL task

A.      First you need to select Connection type. There are following connection type available

Ø  Excel

Ø  OLEDB

Ø  ODBC

Ø  ADO

Ø  ADO.NET

Ø  SQLMOBILE

Generally we are using OLEDB connection Type.

B.      Connection: - here you can create a new connection according to connection type.

Select server name click on ok.

A.      SQLSourceType:-  there are three type of SQL Source Type

Ø  Direct input

Ø  File connection

Ø  Variable

Here I am using direct input.

Direct input: - you can write the SQL quires in the direct input. Like select, insert, update, delete.

Click on ok. Execute it.

Green means package execute successfully.

A.      Result Set: - there are four types of result set available.

Ø  None: - The None result set is used when the query returns no results. For example, this result set is used for queries that add, change, and delete records in a table.

Ø  Single row: - The Single row result set is used when the query returns only one row. For example, this result set is used for a SELECT statement that returns a count or a sum.

Ø  Full result set: - The Full result set result set is used when the query returns multiple rows. For example, this result set is used for a SELECT statement that retrieves all the rows in a table.

Ø  XML: - The XML result set is used when the query returns a result set in an XML format. For example, this result set is used for a SELECT statement that includes a FOR XML clause.

B.      Parameter Mapping: - SQL statements and stored procedures frequently use input parameters, output parameters, and return codes.

Insert into TEST (NAME) values (?).

Here we need to map a parameter. First we need to click on Add. Select the User Variables or system variables.

Ø  Select the variable name

Ø  Select proper data type

Ø  Give the parameter sequence

Ø  Set the parameter size.

Click on ok.

A.      Result set: - result set is used when the query returns multiple rows. For example, this result set is used for a SELECT statement that retrieves all the rows in a table. And data type is object.

Result name is the sequence of the output value.

Variable Name: - this is used to store the dataset value.