How to get the database schema from an ODBC Source

I am using QODBC, an ODBC driver for Quickbooks, as a data source in an SSIS project.  I need to create the tables in MS SQL to receive the values from the ODBC source, but i don't know the schema of the source database (quickbooks).  Is there a good program to basically plug into the odbc source and have it learn the schema, maybe even give it to me in a form i can use to recreate it in MS SQL?

Thanks!
LVL 1
jdroger2Asked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
Why not use:
   SELECT * INTO sql_table FROM quickbooks_table
      WHERE 1=2

if you want the data, leave off the WHERE clause
0
 
jdroger2Author Commented:
That's interesting and I guess I could do it for each table in the database, but I'm not sure where I would actually run that query because I don't think I can link the quickbooks DB to the MSSQL because the QODBC drivers are only 32bit and my SQL Server is x64.  Is there a way around this?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This query would help you return all Schemas available in SQL Server database to which you are connecting.

SELECT * FROM sys.schemas
WHERE principal_id = 1
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
nmcdermaidConnect With a Mentor Commented:
It might be difficult to see the quickbooks schema. Last time I used  as the QODBC driver, it didn't connect to the database - it used screen scraping instead. This means its incredibly slow.
There are a number of ways to transfer data between 32 bit drivers and 64 bit SQL systems but I'll let you sort out your schema issue first.
 
0
 
jdroger2Author Commented:
So far I've been using the Import and Export Wizard (it wont complete) but it will get me to the point where I can choose edit SQL and see the SQL it has generated for the new table it would create, then I run that in SSMS.  ITs slow going and envolves some trial and error but it mat end up getting the job done :)  

I also built a second server today on x86 version of windows 2008 so hopefully I can weed out some of the 64 vs 32bit issues, at least for now.  I was hoping to use like 32 gigs of ram for this but I guess 4 will do for now.  

You are SO right about the QODBC driver being SLOW... O well...
0
 
nmcdermaidCommented:
You're not going to be able to get enough data out of Quickbooks to require a 32G server! AFAIK Quickbooks isn't even a server based database, its a client database like access.
You should try for some large scale data transfers out of Quickbooks as soon as possible - you might find that the time this takes is too prohibitive to continue.
0
 
jdroger2Author Commented:
We are using Quickbooks enterprise 9.  That is a good idea about the transfers As soon as possible I will test a large transfer.  The reason for the RAM is the other side of the project, which is connecting to 7 Sybase servers that it has to get data from also...
0
All Courses

From novice to tech pro — start learning today.