Tuesday, August 2, 2016

EXASOL - Using SSIS to load our Data Warehouse

We're using SSIS to populate an EXASOL data warehouse. In our case, the SSIS design is being done using the 2016 version of SQL Server Data Tools - for Visual Studio 2015 ( download the latest SSDT's here: Download SQL Server Data Tools (SSDT)).

Install the EXASOL ADO.NET Driver

If you have not yet installed the EXASOL ADO.NET driver, download and install it. Find the Download Section here:  EXASOL Download Section. These are ordered by version, so make a note of the version you just downloaded. We are using version 5.0.15.

EXASOL also has an ODBC driver, both 32 bit and 64 bit. You can opt to use this, rather than ADO.NET. Overall, the process is similar.

Data Types

The data types in EXASOL have distinct differences from those in SQL Server - especially dates. So before you get started, you'll want to map out both the source and target data types, along with any transformations required. For a comparison of EXASOL and SQL Server data types, along with methods on transforming data see: Load EXASOL from SQL Server: Data types & Conversion.

In general, we found that it's best to use a view in SQL Server, or a SQL Command as part of the OLE DB source task. You can also use a Data Conversion Task to make your conversions. However, we believe using the database engine is more efficient for these types of transformations.

Data Flow

Once you have your data sourced, converted, transformed and setup, the final step is loading data into EXASOL. First, create a new connection to EXASOL using the Connection Manager and the ADO.NET driver you just installed. It will look something like this:

ADO.NET Destination

Setting up an ADO.NET destination task to EXASOL is fairly straight forward, with just a few minor considerations. On the Connection Manager page of the ADO.NET Destination Editor, chose the desired connection. In our example, its

No tables or views could be loaded

At least for now, the drop down combo box does not display a list of tables. No worries, just type in the name of the table, ideally using both the schema and the table name:  <schema>.<tablename>. The example below has TESTDATA.TESTDATA.


Use the Preview button to test the connection.  You should see a preview of your target table, along with a few sample records.


Use the mappings section to verify or update the mappings, Identical names are mapping automatically. All others have to be done manually.

That's it. If you have any loading errors, focus on data types, and especially dates. We also found that tinyint required an explicit data type conversion.  See: Load EXASOL from SQL Server: Data types & Conversion.

Next Steps:  ROLAP - now a viable option
                     Build an SSAS ROLAP Cube using EXASOL

No comments: