Posted Thu, 18 Oct 2018 13:22:29 GMT by Milan Hricko

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) { }

Posted Fri, 10 May 2019 23:49:19 GMT by jacob alley
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.
Posted Fri, 10 May 2019 23:49:20 GMT by Milan Hricko
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();
Posted Fri, 10 May 2019 23:49:21 GMT by jacob alley

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/

Posted Sat, 22 Aug 2020 16:20:28 GMT by Leonard Mundia

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. 

You must be signed in to post in this forum.