We help IT Professionals succeed at work.

SSIS: get table names from source MySQL database

Hi experts,

I am learning SSIS (Visual Studio 13, SSDT).  I plan to populate a SQL Server database on my local machine with tables from a remote MySQL server.  

I connected to the MySQL server using an ODBC connection and successfully created and populated a single named table on my local MSSQL database (OLE DB connection).  

So far so good.  Now I want to iterate thru all the MySQL tables in the source database and do the same.  How would I do that?

Thanks,

  Colin
Comment
Watch Question

Top Expert 2011

Commented:
Just execute this query and iterate through the result set:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA = 'YourDatabase';

Open in new window


PS: There are extra columns you can include in the WHERE Clause, to filter unwanted entries. Execute 'SELECT * FROM...' and see if the result is clear.

Author

Commented:
Thanks for responding.  I'm looking for help with SSIS rather than anything else...use of variables and loop etc.

Author

Commented:
From searching, I don't think this can be done without a script since all the tables are configured differently.  And I don't know how to write such a script.
Top Expert 2011

Commented:
Sorry can't help with this one.

You can request the moderators attention (Link above) to send an email to other experts.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Which object are you using in SSIS to import the MySQL table? It should give you option to chose more than one table to import.

Author

Commented:
Yes but if the tables are structured differently, have different columns etc, I'd need to script it?  As I understand it SSIS needs to know the metadata beforehand.

I'm learning SSIS and thought I could back up my MySQL DB to MSSQL, but it's not a critical operation.

Thanks.

Col
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes but if the tables are structured differently, have different columns etc, I'd need to script it?
What do you mean? You're trying to import into tables that already exists and with different structure?

Author

Commented:
Pretty much.  They won't exist the first time through.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
They won't exist the first time through
So at the 2nd time they'll keep the name but with a different structure?

Author

Commented:
No, the first time the tables won't exist.  After that I'll truncate the tables before reloading them.  But each table will have a different structure.

Please also look here, a separate issue.  http://www.experts-exchange.com/Programming/Microsoft_Development/Q_28648925.html
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Ok, I understand now but you should not have any kind of issues. Just tick the tables that you want to be exported and for each one you can chose to delete all rows before import.

Author

Commented:
I need to have a separate data flow for each table?  I was hoping to get away with a single data flow, perhaps in a Foreach loop.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, A Foreach Loop Container should do the trick.
No, it cannot be done in SSIS....what do I iterate on in the Foreach loop?  You cannot say "SELECT * FROM {?table_name} where the table name is a parameter.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
I think this article gives you a good idea how to use it with a query.

Author

Commented:
Yes, I saw that.  I'll use SSMA instead.  Cheers.

Author

Commented:
The expert's comments were helpful but I had to do a lot of the investigation myself.  Hope that's OK.