SQL Server 2005 Integration Service Basic

Introduction

SQL Server Integration service is mainly used to transfer data from one database to another in different format. Also it is used to manipulate data before storing on destination.

SSIS (SQL server Integration Service) is specially designed for ETL process. ETL stands for Extract, Transform and Loading.

SSIS create package which you can execute from the SQL server management studio, stored procedure or SQL server job which extract data from one database, manipulate it and store it in another database. Here both databases may be different.

You can use any of the following database connection with SSIS package:

• SQL Server


• Flat File


• OLEDB


• ADO.Net


• Analysis Service

Prerequisite:
We need SQL Server Business intelligence development studio (BIDS) tool to develop SSIS package. BIDS tools come with Microsoft SQL Server Installation. Installing Microsoft SQL Server, Please check “Analysis Service” and “Integration Service” option.

In this blog, I will explain you how to create simple SSIS package and execute it from the SQL Server Business Intelligence development studio.

Open SQL Server Business Intelligence Development studio. Follow the following steps to create new SSIS package.

• Create new project from “File” menu -> “New Project”.


• Select “Business Intelligence Projects” from Project types in New Project dialog box


• On right side, you can see different Template available for “Business Intelligence Projects” type. Select “Integration Service Project” template from the available template.


• Give appropriate project name. Here I am giving “DemoIntegrationProject”


• Select Location where you want to save the file. And also give solution name. I am giving “DemoIntegrationSolution”.


• Click on Ok button to create new SSIS project.

The New project dialog box will display as below:



BIDS creates new project and open that project. You can see the available files and objects in Solution explorer. Open solution explorer if it is not opened.

You get four folder in solution explorer included into the project.

• Data Sources: This folder contains information about all data source used into the project.


• Data Source Views: This folder contains the views (UI) of used schema from the data sources.


• SSIS Packages: This folder contains all SSIS packages.


• Miscellaneous

Projects template add one blank SSIS package “Package.dtsx” into the project. We will first understand “Data sources” and “Data Source Views” to understand SSIS package.

Data Sources:
Select “Data Sources” folder in solution explorer. Right click on that. You will get “New Data Source…” option to create new data source. Just click on “New Data Source…” option.

“New Data Source…” option open wizard to create and add new data source into project. Follow the below steps to create new data source:

• On welcome screen, click on Next button


• On “Select how to define the connection” screen, you can see existing data connections (if you used SSIS before and added into any project). Refer screen below
  
  
  


• Select data connection if exist in the list. If your required connection is not available then you can create new connection using “New…” button.


• If connection is not already exist then click on “New…” button. It will open connection manager dialog. On Connection Manager dialog, select appropriate provider. Provide information necessary to create new data connection. Test your input information using “Test Connection” button. If your connection test successfully then Click on Ok to create new connection. Refer screen below
  
  
  


• You will get your connection on “Select how to define the connection” dialog. Select your connection and click on Finish button


• Give your connection name and click on Finish button.

Now you will get new data source file under “Data Sources” folder in solution explorer.

Data Source Views:
You have to create data source views before using data source into SSIS package. To create new “data source view”, select “Data Source Views” folder in solution explorer and right click on it. Select “New Data Source View…” option.

New Data Source View dialog open. Follow the below steps create new data source view:

• On welcome screen, click on Next button


• On “Select a data source” screen, select the data source which you have created. You can create new data source using “New Data Source…” button.


• After selecting data source, click on next button


• On “Select Tables and Views” dialog box, Add required tables to Included objects list box. You can add related objects using “Add Related Tables” button. You can also filter available objects using Filter text box. Add all required tables and views and click on Next button. Refer screen below:
  
  
  


• On “Completing the wizard” screen, give “Data source view” name (Here I gave it to “Test DB”) and click on Finish button.


• It will add new data source view file in the project.

Data Source View editor have three panes: Diagram Organizer, Tables, and Diagram.

Diagram Organizer: Diagram organizer pane allows you to create new Diagram and organize table into sub set. If you have 500 tables/views on Data Source view, then it will be difficult to refer any particular table or related tables. So you can make different Diagram using Diagram organizer pane. Just right click on “” and select “New Diagram” option. It will open new diagram where you can add only your required tables from “Tables” pane.

Tables: Tables pane contains all tables and views selected while creating data source view. After creating data source view, you can add/remove any objects. To add new table/view, just click on “Add/Remove objects” button in tool bar. It is displayed just above Diagram organizer pane.

Diagram: Diagram pane displays all tables and views schema and relation between them.

You can rename table/view and your field name. Sometime your database table/view name is not user friendly like the above diagram “tblProducts” table name is not user friendly. To rename table/view/field name, just select it in Diagram pane and open properties windows. In properties window, change FriendlyName property to give some meaningful name. I have change table names and the updated diagram is displayed as below:



SSIS Package:
You can create new SSIS package using right clicking on “SSIS Packages” folder in Solution explorer and select “New SSIS Package” option.

After package file is added into the solution you can rename to give proper name. When you will rename the package, it gives message to open the SSIS package click on yes to open the package.

SSIS package editor have four tabs:

• Control Flow


• Data Flow


• Event Handlers


• Package Explorer

Control Flow: You can add any number of control flows on your SSIS package. Control flow is just like a one task which you want to accomplish using SSIS package. BIDS tool have many integrated tools available to work with your project. Following are the most usable control flow tasks:

• Bulk Insert Task


• Data Flow Task


• Data Mining Query Task


• Execute Package Task


• Execute Process Task


• Execute SQL Task


• For loop container


• Foreach loop container


• FTP Task


• Script Task


• Send Mail Task


• Web service Task


• Xml Task

There are many other tasks available to create SSIS package as per your requirement.

Data Flow: Data flow is detailed definition of “Data Flow Task” which is available to add in Control Flow. You have seen “Data Flow Task” item available in above list to add into Control Flow tab. When you will add Data Flow Task, you can see those data flow task name in “data flow task” combo box in Data Flow tab.





Now we will create one sample SSIS package, deploy it and execute it from Business Intelligence development studio.

• Open your SSIS Package and open “Control Flow” tab.


• In Control Flow tab, Right click in “Connection Managers” pane and select “New Connection from Data Source…”


• It will open “Select Data Source” dialog box where you can see the Data sources added in your project.


• Select the proper data source and click Ok button. It will add that data connection into your SSIS package. Refer screen below:

• After successfully adding connection manager, Add Data Flow task control on your control flow.


• Right click on Data Flow Task in Control Flow tab and select Property. Change name property from “Data Flow Task” to “Export Customer Data”


• Go to Data Flow Tab


• Add “OLE DB Source” Task on “Data Flow” tab


• Double click on “OLE DB Source” task to open OLE DB Source Editor


• In editor, Select Connection Manager, Data Access mode and other required items. For demo purpose select “Test DB” as a connection manager, “Table or View” as a data access mode and select tblCustomers as a table.


• Click on Ok button.


• Now drag and drop “Flat File Destination” control on Data Flow tab


• Select “Ole Db Data Source” control, you can see green and red arrow. Just hold green arrow and put it on “Flat file destination” control.
  
  Green arrow – Green arrow is used to follow the process if preceding task is completed successfully. In our case, if we get customer data successfully then only go to flat file destination to export it.
  
  Red arrow – Red arrow is used to follow the process if preceding task is completed with error. You can use “Send Mail” task to send the error information or save those error in another destination.

• Double click on “Flat File Destination” control to open control editor


• Create new connection for Flat file. Click on New button to create new connection.


• Select “Delimited” option to generate Flat file connection manager.


• Give proper name and browse the file path


• Click on Ok to add connection manager to your project.


• Click on ok in “Flat File destination editor”

Now you simple package is ready. This package will read customer data from the database and put it in Flat file (Comma separated). You can also use another controls to process the data. You can also filter the data in OLEDB Source control.

When you will execute SSIS package, Business Intelligence development studio will execute your task and transfer customer data from database to your flat file.

Still you have not deployed SSIS package. You can deploy it in SQL server. Follow the below steps:

• Open SQL Server and login on server using Integration service. (In login dialog box, you have to select server type as a Integration service)


• After successfully connecting, expand “Stored Packages” in object explorer.


• Expand “File System”.


• Right click on “File System” and choose “Import Package”


• In Import package dialog, Select “File System” as a Package location


• Select the Package path. Browse to the path where we have created the above SSIS package project and select DTSX file.


• Give the package name and click on ok button to create the package.

You can see your SSIS package under “File System” node in object explorer of SQL server management studio.

You can execute SSIS package from SQL server management studio. Right click on “OrderSSIS” package and choose “Run Package” option. It will open “Execute Package Utility” dialog. Click on Execute button to run the package.