Integration Services, Part 1 — Import and data processing in Firebird and InterBase.
IntroductionSSIS 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 codeKeywords: 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).
|
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:
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:
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).
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).
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:
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.
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:
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:
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:
Further data are transferred to Aggregate Component (Group by countries and items types), which makes different group operations concerning resulting set:
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:
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:
Topic references.
- Download IBProvider Professional Edition (32/64 bit)
- FAQ: Set up MS SQL Server Business Intelligence (SSIS, SSRS, SSAS) for Firebird, InterBase [en].
- FAQ: Set up Firebird/InterBase для работы с MS SQL Linked Server [en].
- Connect to Firebird/InterBase from .Net.
- ODBC Firebird driver, ODBC InterBase or OLE DB?.
- General IBProvider documentation.
You may also subscribe on news and gets other future parts of this article series.