mv.SSIS: Extending MultiValue Data Integration

mv.SSIS is a SQL Server Integration Services (SSIS) component which provides complete integration of the mv.NET ADO.NET provider with the SSIS environment.  This allows drag-and-drop creation of standard SSIS packages within the SSIS design studio, providing seamless transfer of data between MultiValue databases and SQL.   

mv.SSIS allows MultiValue users to extract data from any major MultiValue database (in both bulk and filtered export mode) to other data repositories using Microsoft's SQL Server Integration Services (SSIS) product.  The definition, testing and execution of these extraction processes is performed entirely from within the standard SSIS environment and ensures data consistency, eliminates redundant data and saves time.

Introduction

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

mv.SSIS allows data to be read from MultiValue databases into the SSIS environment.  This opens up the entire SQL Server set of tools and utilities to MultiValue database users.

SSIS in Brief

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

SSIS provides a graphical front end to design control flow data processing logic.  Once designed, these ‘packages’ are compiled into ‘.dtsx’ packages which can then be distributed and run on any machine with the SSIS tools installed.

Packages contain two main logic flows, a ‘Control Flow’ which defines a sequence of logical operations which are processed in sequence. Each step is completed before the next starts e.g.  

▪  Empty out work tables in a database
▪  Populate the work tables with data
▪  Perform calculations and update the values in the work table
▪  Update OLAP cubes with the data from the work tables
▪  Run reports against the OLAP cubes

This level of control also allows processing loops to be defined e.g.

For each file in a specified folder, read the contents of the file and write it into a specified table.

The second main logic flow is the ‘Data Flow’.  This allows for the processing of data at the record level. Data is read from a ‘Data Source’ and passes down a series of ‘Data Transformations’ to a ‘Data Destination’. These transformations can be as simple as changing the data type of fields e.g. varchar(4000) to varchar(2000) or decimal(18,2) to decimal(8,2), or can be more complex like data merges, joins, pivot tables, multicasts etc. Each transformation is represented by an icon in the designer and the icons are linked together to define the logic path.

External Links
More information about SQL Server Integration Services can be found on the Microsoft website at the following links:
Integration Services
SQL Server Integration Services