Search code examples
joinssismariadblookup

Is there an alternative way for lookup in ssis for mariadb?

Suppose we have two tables with the following values:

ID Name Fname
1 aaaa aaaa
2 bbbb bbbb
3 cccc cccc
4 dddd dddd
ID AnotherId
1 3
2 5
3 4
4 2

I'd like to add the AnotherId column to the first table as follows:

ID Name Fname AnotherId
1 aaaa aaaa 3
2 bbbb bbbb 5
3 cccc cccc 4
4 dddd dddd 2

This can be easily done with Lookup for SQL server database, but there is no ODBC connection in Lookup for MariaDB database. I have installed ODBC driver and used it many times in ODBC source and ODBC destination, but I can't use it in lookup. It's my lookup provider:

enter image description here

And It's my data source installed driver:

enter image description here

I use merge join and union all to get a table like the one below, which contains 16 records. Furthermore, I tried all inner join and left outer join modes, but the result is the same.

ID Name Fname AnotherId
1 aaaa aaaa 3
1 aaaa aaaa 5
1 aaaa aaaa 4
1 aaaa aaaa 2
2 bbbb bbbb 3
2 bbbb bbbb 5
2 bbbb bbbb 4
2 bbbb bbbb 2
3 cccc cccc 3
3 cccc cccc 5
3 cccc cccc 4
3 cccc cccc 2
4 dddd dddd 3
4 dddd dddd 5
4 dddd dddd 4
4 dddd dddd 2

Is there an alternative way to use lookup in this scenario And achieve to my desire table? Or is there a way for lookup to recognize the installed ODBC driver?

ID Name Fname AnotherId
1 aaaa aaaa 3
2 bbbb bbbb 5
3 cccc cccc 4
4 dddd dddd 2
Solution

  • A lookup component can either use an OLE DB Connection Manager OR it can use a Cache Connection Manager. A Cached Connection manager is what you use when it's not an OLE DB CM.

    In your case, you'd have a precursor/priming Data Flow step. ODBC Source -> Cached Connection Destination (name approximate) Flow the data in there that you need for the lookup and identify your key column(s).

    In your "actual" data flow, you'll change from OLE DB to Cached Connection Manager

    Data flow

    enter image description here

    Fill the cache

    enter image description here