• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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
0
colinspurs
Asked:
colinspurs
  • 9
  • 6
  • 2
2 Solutions
 
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
 
colinspursAuthor Commented:
Thanks for responding.  I'm looking for help with SSIS rather than anything else...use of variables and loop etc.
0
 
colinspursAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
colinspursAuthor 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
 
colinspursAuthor 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
 
colinspursAuthor 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
 
colinspursAuthor 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
 
colinspursAuthor 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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think this article gives you a good idea how to use it with a query.
0
 
colinspursAuthor Commented:
Yes, I saw that.  I'll use SSMA instead.  Cheers.
0
 
colinspursAuthor Commented:
The expert's comments were helpful but I had to do a lot of the investigation myself.  Hope that's OK.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 9
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now