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.
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.
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).
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:
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: