Importing Multiple tables from Access table

Question

Post reply

Importing Multiple tables from Access table

SJB42

SSChasing Mays

Points: 613
More actions
March 3, 2010 at 6:00 am

#139114

Hi,
Is it possible to import multiple tables from an Access database into a SQL Server database in one go?
The problem is that I don't know how many or the names of the tables that may be in the Access database.
I assume I would have to access the matadata of the Access database to get the table information. Is this possible?
Thanks in advance,
Stephen

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Might I suggest that your read:

How to convert an Access database to SQL Server

http://support.microsoft.com/kb/237980

and/or

SQL Server Migration Assistant 2008 for Access V4.0

http://www.microsoft.com/downloads/details.aspx?FamilyID=133b59c2-c89c-4641-bebb-6d04476ec1ba&DisplayLang=en

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

SJB42 SSChasing Mays Points: 613 More actions · Answer 2

Hi Ron,

Thanks for the reply.

A bit more background to my problem.

I can't use manual conversion wizards because the access database will be placed in a folder on a nightly basis and I want SSIS to pickup the Access database, determine what tables are in the access database and perform specific processing based on the type of table which will ultimately include copying the data into a SQL Server table.

Thanks,

Stephen

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

What do you mean by "type of table"?

--Edit

are all of the physical tables already set up in SQL Server, or are you hoping to do some sort of create-then-import process?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SJB42 SSChasing Mays Points: 613 More actions · Answer 4

Hi Phil,

Sorry, I should have been clearer.

When I mean type of table I actually mean the type of data in the table. There will be a number of tables in the Access database holding specific types of data. If I find table X in the database then it will hold data of type X and I will do specific processing for X type data. If I find table Y then it will hold data of type Y and will process accordingly.

The physical tables are already set up in SQL Server. I will be inserting the access data into the SQL Server tables.

Sorry again, I should have been clearer in my original post.

Thanks,

Stephen

Phil Parkin SSC Guru Points: 246991 More actions · Answer 5

So it sounds like you want to be able to repeatedly (at defined periods) process an Access database. (Is there only one? Will it always have the same name?).

Every time the database is processed, you want to iterate through the collection of user tables.

For each table found, depending on name, you will perform an import of its data into a specified target table on SQL Server - so there is a one-to-one mapping from table name on Access to table name on SQL Server.

Does that sum it up?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SJB42 SSChasing Mays Points: 613 More actions · Answer 6

Hi Phil

thanks for your reply.

Yes, you have the requirement spot on.

The access db can be names differently.

There maybe more than one access db but they will be processed sequentially.

I've looked at using the foreach loop container with the ado.net schema enumerator to get access to the tables in the Access db. However I seem to be having problems with the variable I've created to capture the table name. I've created a string variable but it errors saying the variable type is not the same the type being assigned to to it. Maybe my variable needs to be of type object?

Thanks again

Stephen

Phil Parkin SSC Guru Points: 246991 More actions · Answer 7

Yeah - I reckon you'll get more than just the table name by doing that. If you try 'object', does the error go away?

You might need a bit of script to get at the properties of the table object (.Name, perhaps?). Not sure.

Nested Foreach loops look like the way forward for you - the outer one processes all the Access database files and the inner one the tables themselves.

Good luck.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SJB42 SSChasing Mays Points: 613 More actions · Answer 8

Hi Phil,

Thanks again for your reply.

Yes, walking home I was thinking that the loop container must be sending more back than just a single table name.

I'll investigate setting my variable to object and see how that goes tomorrow. I guess I could use the foreach loop container with the object enumerator to access the data in the object maybe.

Cheers,

Stephen

SJB42 SSChasing Mays Points: 613 More actions · Answer 9

Hi Phil,

Regarding the issue I was having with the ADO.NET Schema Rowset enumerator. It was all down to the index used in the variable mapping screen. The default value is 0. But, as we suspected, we're getting more than one value back from this enumerator. If you use index 2 you get the table name and you can assign it to a string variable.

I can't find any documentation anywhere that details the values you get back from this enumerator.

Thanks again for your help,

Stephen

Phil Parkin SSC Guru Points: 246991 More actions · Answer 10

Trial and error strikes again! I'm afraid I can't help you any more, but well done on getting there in the end.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SJB42 SSChasing Mays Points: 613 More actions · Answer 11

Hi Phil,

Cheers. I think I'm in business now. Now that I can determine what tables are in the Access database I can direct the flow to process the tables accordingly.

Thanks,

Stephen