Publish date: 2008-02-18
Updated: 2015-07-29

Integration Services, Part 1 — Import and data processing in Firebird and InterBase.

Introduction
SSIS package and Data Flow creation
Connection to Firebird/InterBase database.
Data import on the table basis. OLE DB Source
Data Import on SQL-query basis
Data transformation
Topic references

Download sample source code

Keywords: MS SQL Server Integration Services, Business Intelligece (BI), BI systems, MSSQL 2005, Firebird, InterBase

Introduction.

ETL — Extract, Transform, Load

SQL Server Integration Services (SSIS) — is a comfortable way for developing ETL processes. Integrations Services Projects are well integrated with other development tools of SQL Server 2005 — Analysis Services (SSAS) and Reporting Services (SSRS). Excel workbooks, text and XML files as well as databases with OLE DB Provider realization may be both data sources and data receivers. To work with Firebird и InterBase data в Integration Services you should use OLE DB Source and OLE DB Destination Components. Also you should install IBProvider Professional Edition (32/64 bit).

OLE DB Source. Integration Services         OLE DB Destination. Integration Services

Integration Services lets applying users sources and data receivers based on Components. More information about Components’ creation for Integration Services you may read in the article Rev Up Performance with Custom Connectors, Douglas McDowell, Jay Hackney

Let’s create as an example of SSIS package, in which the data about company’s clients and the sales reports from database coming as a part of Firebird distributive will be downloaded. Further we’ll make the simplest data converting and show the sales statistics, grouped according to countries, on screen.

SSIS package and Data Flow creation.

Run SQL Server Business Intelligence Development Studio and create the new Integration Services Project. Drag Data Flow Task Component to Control Flow Tab (Picture 3). The new data flow will be created. Further switch to Data Flow Tab and drag OLE DB Source from Toolbox. Picture 4. Then you should customize data source.

Connection to Firebird database.

There are some ways to get connected to database. One of them is to use the Connection Manager. Press mouse right button on Connection Manager and choose New OLE DB Connection:

SSIS. Connection manager

In opened window OLE DB Connection Manager (Picture 6) you should choose the existed connection or create the new one. In second case the dialogue where you should choose LCPI OLE DB Provider for InterBase [v3] from the list appeared and press the Data Links button will be opened:

SSIS. Create OLE DB Connection

When the OLE DB dialogue of connection setup embedded in IBProvider is running you should:

  • Full path to database file — correspond to connection string parameter Location.
  • User and Password.
  • Save password — let password saving in connection string text.
  • Enable Automatic transaction — correspond to connection string parameter auto_commit).

Data Link. Set up Firebird/InterBase datasource

Go to the next tab:

  • Choose server type.
  • Set up the ticks Support ODBC Query correspond to connection string parameter support_odbc_query).
  • Put off the ticks Truncate Char — correspond to connection string parameter truncate_char).

Data Link. Set up Firebird/InterBase mandatory properties

And finally you have to set up new property support_square_delim_name = 1 of IBProvider on the last tab (Picture 10).

So the connection setup is finished. To be sure in the right way of settings you are to press Test Connection button.

Data import on the table basis. OLE DB Source.

For clients list import we create the data source that download Customers table to Integrations Services. Click mouse right button on OLE DB Source Component (in the sample it’s Customers List) and choose Edit:

Manage OLE DB Source

The dialogue of data source setup will be opened. There you should:

  • Choose database connection.
  • Choose table with data (CUSTOMERS table).
  • Customize returned fields list, chosen table.
OLE DB Source based on table

Data Import on SQL-query basis.

To download sales report we should use OLE DB that will receive data on SQL-Query basis. We add OLE DB Source Component to Data Flow and customize it as it is shown in the picture:

OLE DB Source based on SQL query

Query may be written manually or you may use Query Builder. To check syntax press Parse Query, Preview button shows query processing result.

Data Transformation.

In Integration Services many standard converters are realised. Their review doesn’t match the topic of this article, so I’ll show the resulting data stream:

SSIS. Merge Join Transformaion

Clients and sales data are downloaded from two OLE DB Sources — Customers List и Sales Report. Further they are sorted CUST_NO field with the help of two Sort Components. Preliminary sorting is needed for Merge Component (Merge customer countries and sales info). In Merge Component data from two sources are grouped by CUST_NO field. The TOTAL_VALUE, DISCOUNT, ITEM_TYPE fields from sales report and COUNTRY field from clients’ list are added to resulting set:

Firebird/InterBase Data Flow

Further data are transferred to Aggregate Component (Group by countries and items types), which makes different group operations concerning resulting set:

SSIS. Agregate Transformation

As it is shown in the picture, firstly comes countries grouping, then goods type grouping, and then the profit value and sales number in every country is defined. After that Sort Component sorts data according to profit value and transmit them to virtual receiver- DataReaderDest. Data processing result is controlled with the help of Data Viewers:

SSIS. Add Data Viewer

DataViewers may be easily used for showing the preliminary results and SSIS package debug. After package running (F5), window with data processing results will appear on the screen:

SSIS. Data Viewer

Topic references.

You may also subscribe on news and gets other future parts of this article series.

Other useful references

Author: Andrew A. Merkulov  

Publish date: 2008-02-18. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link http://www.ibprovider.com/eng required.