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.
ODBC:
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.
ODBC:
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 192.168.1.100:8563.sys.
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.
Preview
Use the Preview button to test the connection. You should see a preview of your target table, along with a few sample records.
Mappings
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:
Post a Comment