How to get metadata from MariaDB (MySQL)

Hi, my source is MariaDB connected via ODBC. I would like to generate SSIS for all tables in the source DB. How should I define connection to get metadata (list of all tables) ? I found a code snippet that works for OleDB :

https://www.mssqltips.com/sqlservertutorial/9094/biml-tutorial-retrieving-metadata-with-getdatabaseschema/.

 

Is it possible to get metadata via ODBC connection or should I use Ado.NET connector? My BIML code looks like this. I am expecting list of tables on the output but it returns no table: var sourceConnection = SchemaManager.CreateConnectionNode("Source", "Server=sourceserverx.;Port=3306;Database=dwh;Uid=dwh;Pwd=xxx;"); var sourceMetadata = sourceConnection.GetDatabaseSchema(); foreach (var table in sourceMetadata.TableNodes) { }

0

Comments

4 comments
  • We are planning on OpenSourcing GetDatabseSchema at some point, so that the community is able to support any database/provider if they so choose. If you are not having luck with your connection it may be because there are a few connection types that do not work 100% with GetDatabseSchema. I have found that I have had more luck with ImportDB() with those less traditional (in the context of SSIS) connections/databases.
    0
    Comment actions Permalink
  • can you send me the snippet for using ImportDB()? I found something and I am trying but I am getting the error "Object reference not set to an instance of an object." My BIML code: var sourceConnection = (AstOdbcConnectionNode)RootNode.Connections["conn_SOURCE"]; var importedSchema = sourceConnection.ImportDB();
    0
    Comment actions Permalink
  • This article written by Biml Hero, Cathrine Wilhelmsen, has ample information on ImportDB(). https://www.cathrinewilhelmsen.net/2014/12/08/import-metadata-biml-importtablenodes-and-importdb/

    -1
    Comment actions Permalink
  • Jacob, I think you are missing the question in all these ODBC requests, Cathrine has a done a phenomena job in her scripts but they call a SQL Connection (SchemaManager.CreateConnectionNode) which is an AstOleDbConnectionNode function. How do we transform it to AstOdbcConnectionNode? I get the same Object reference not set to an instance of an object. 

    0
    Comment actions Permalink

Please sign in to leave a comment.