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
LVL 3
Colin BrazierAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
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.
0
Colin BrazierAuthor Commented:
Thanks for responding.  I'm looking for help with SSIS rather than anything else...use of variables and loop etc.
0
Colin BrazierAuthor 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.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

jimyXCommented:
Sorry can't help with this one.

You can request the moderators attention (Link above) to send an email to other experts.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Colin BrazierAuthor 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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Colin BrazierAuthor Commented:
Pretty much.  They won't exist the first time through.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
They won't exist the first time through
So at the 2nd time they'll keep the name but with a different structure?
0
Colin BrazierAuthor 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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Colin BrazierAuthor 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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, A Foreach Loop Container should do the trick.
0
Colin BrazierAuthor Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think this article gives you a good idea how to use it with a query.
0
Colin BrazierAuthor Commented:
Yes, I saw that.  I'll use SSMA instead.  Cheers.
0
Colin BrazierAuthor Commented:
The expert's comments were helpful but I had to do a lot of the investigation myself.  Hope that's OK.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.