SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need advice on how to process Access DBs with different table structures into SQL Server


Need advice on how to process Access DBs with different table structures into SQL Server

Author
Message
pharmkittie
pharmkittie
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 920
I'm looking for some guidance on how to use SSIS to process Access database files into SQL Server. I know how to process flat files thanks to a lot of good tutorials I've found but when it comes to Access DB files I've only seen cases where all the tables in the Access DBs are of the same structure. I have a directory of Access DB files that I want to import to SQL Server then move to an archive directory. Should I use For Each Loop Containers with a separate one for each table type in the Access DB? In other words, if I have an Access file it would be opened again each time a new table within it is imported? It seems like there should be a way to open the Access DB once then process the different tables but I don't know how. There can be any number of Access DBs with different dates in the their names so I would use the foreach loop to process as many as there are in the directory, moving each one to archive after all tables are imported. Thanks in advance.
pietlinden
pietlinden
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 16130
You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to linked table etc. I think when you have tables without standard naming conventions, it's easier in Access (maybe because I'm terrible at SSIS). But you can loop over a table's Fields collection, and it's pretty trivial. Also, if you have information stored in a table's name, you can use TableDefs(n).Name to stuff that into a variable and chunking it and figuring out what to do with it from there.

Just another option. Sorry it's not a direct answer to your question... I just find fixing screwy designs easier using DAO than using SSIS.
pharmkittie
pharmkittie
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 920
pietlinden - Saturday, March 3, 2018 5:59 PM
You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to linked table etc. I think when you have tables without standard naming conventions, it's easier in Access (maybe because I'm terrible at SSIS). But you can loop over a table's Fields collection, and it's pretty trivial. Also, if you have information stored in a table's name, you can use TableDefs(n).Name to stuff that into a variable and chunking it and figuring out what to do with it from there.

Just another option. Sorry it's not a direct answer to your question... I just find fixing screwy designs easier using DAO than using SSIS.

Thank you. I appreciate your answer but I am not an Access developer. I have been asked to use SSIS. Cheers.

pietlinden
pietlinden
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 16130
The reason I asked about Access is that I don't know how to dynamically select which columns are getting imported/mapped to which destination columns in SQL Server. I have done that in Access, but never in SSIS.
pharmkittie
pharmkittie
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 920
Thanks pietlinden. I recognize your name and I think you've helped me before a long time ago. I'm afraid I don't know how to program Access. The same tables will be in each Access database so I can fall back on defining each table individually as I mentioned in my original post unless there's another way to do it in SSIS.
sgmunson
sgmunson
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74397 Visits: 6358
pharmkittie - Saturday, March 3, 2018 1:05 PM
I'm looking for some guidance on how to use SSIS to process Access database files into SQL Server. I know how to process flat files thanks to a lot of good tutorials I've found but when it comes to Access DB files I've only seen cases where all the tables in the Access DBs are of the same structure. I have a directory of Access DB files that I want to import to SQL Server then move to an archive directory. Should I use For Each Loop Containers with a separate one for each table type in the Access DB? In other words, if I have an Access file it would be opened again each time a new table within it is imported? It seems like there should be a way to open the Access DB once then process the different tables but I don't know how. There can be any number of Access DBs with different dates in the their names so I would use the foreach loop to process as many as there are in the directory, moving each one to archive after all tables are imported. Thanks in advance.

I've read all the other answers so far, but to be quite honest, SSIS just is NOT the right tool for the job. Microsoft created SSMA, or SQL Server Migration Assistant, to do this kind of work. Just be sure to get the right version for the combination of Access DB version and SQL Server version. It's relatively simple to use, but does require some thought and experimentation the first few times, so be prepared to drop the tables it creates and start over. You will likely have to do some massaging of it's default setup for a given table with regard to data types, but in the long run, it's the only really decent tool for the job. Using SSIS for such a task is probably not worth the trouble.


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
pharmkittie
pharmkittie
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 920
sgmunson - Thursday, March 8, 2018 10:00 AM
pharmkittie - Saturday, March 3, 2018 1:05 PM
n Access DB I'm looking for some guidance on how to use SSIS to process Access database files into SQL Server. I know how to process flat files thanks to a lot of good tutorials I've found but when it comes to Access DB files I've only seen cases where all the tables in the Access DBs are of the same structure. I have a directory of Access DB files that I want to import to SQL Server then move to an archive directory. Should I use For Each Loop Containers with a separate one for each table type in the Access DB? In other words, if I have an Access file it would be opened again each time a new table within it is imported? It seems like there should be a way to open the Access DB once then process the different tables but I don't know how. There can be any number of Access DBs with different dates in the their names so I would use the foreach loop to process as many as there are in the directory, moving each one to archive after all tables are imported. Thanks in advance.

I've read all the other answers so far, but to be quite honest, SSIS just is NOT the right tool for the job. Microsoft created SSMA, or SQL Server Migration Assistant, to do this kind of work. Just be sure to get the right version for the combination of Access DB version and SQL Server version. It's relatively simple to use, but does require some thought and experimentation the first few times, so be prepared to drop the tables it creates and start over. You will likely have to do some massaging of it's default setup for a given table with regard to data types, but in the long run, it's the only really decent tool for the job. Using SSIS for such a task is probably not worth the trouble.

Thank you but the incoming files will always be Access files so I need a way to process them via an automated, unmanned job. This is one of many projects that will have files coming from external entities that need to be imported. I can do Excel and flat files but I need advice about how to use SSIS to import Access DB tables..

pietlinden
pietlinden
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 16130
"automated unmanned job" - the "processing database" would have an autoexec macro in it that kicks off the processing of the databases in some folder (uses DIR() to loop over the databases). You'd just call it using something like /x:macroName.
Then maybe create a job in SSIS to run that... ? I want to say I vaguely remember Danny Lesandrini posting something about that a long time ago. I might have to ask him.
In a nutshell, you have an Access "front end-ish" database that connects to (uses linked tables) other "source" databases, and walks the Fields collection in of some of the tables in there. (DAO fun). Then you can do something based on what Fields are in the collection.

Steve - say I create a job in SSIS that kicks off a non-SQL Server thing - like a script that just opens the Access database that contains the autoexec macro (basically "DatabaseX".. always in the same place)... I can just script that part in SSIS, and then Access takes over, right?
pietlinden
pietlinden
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48182 Visits: 16130
pharmkittie - Saturday, March 3, 2018 9:23 PM
pietlinden - Saturday, March 3, 2018 5:59 PM
You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to linked table etc. I think when you have tables without standard naming conventions, it's easier in Access (maybe because I'm terrible at SSIS). But you can loop over a table's Fields collection, and it's pretty trivial. Also, if you have information stored in a table's name, you can use TableDefs(n).Name to stuff that into a variable and chunking it and figuring out what to do with it from there.

Just another option. Sorry it's not a direct answer to your question... I just find fixing screwy designs easier using DAO than using SSIS.

Thank you. I appreciate your answer but I am not an Access developer. I have been asked to use SSIS. Cheers.

If the fields in the Access tables don't match, how are you going to map them? I had to do this a long time ago... pretty serious hassle. You could create a "dictionary" of sorts of the field names in the source databases, and then write the unique values to a table and then add another column so you get (SourceColumnName, DestinationColumnName) pairs. Then you'd connect to a new Access "backend" database, loop of the the fields in the table you need, and create the insert statement from the value pairs, and then execute the dynamic SQL. Then go to the next database. You can loop over the directory using DIR etc. (Or use a FileSystemObject).

pharmkittie
pharmkittie
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 920
pietlinden - Thursday, March 8, 2018 6:13 PM
pharmkittie - Saturday, March 3, 2018 9:23 PM
pietlinden - Saturday, March 3, 2018 5:59 PM
You can do this in Access too... Open a front end (no data, just linked tables, and some code). Then loop through the table(s) and deal with them. Append to linked table etc. I think when you have tables without standard naming conventions, it's easier in Access (maybe because I'm terrible at SSIS). But you can loop over a table's Fields collection, and it's pretty trivial. Also, if you have information stored in a table's name, you can use TableDefs(n).Name to stuff that into a variable and chunking it and figuring out what to do with it from there.

Just another option. Sorry it's not a direct answer to your question... I just find fixing screwy designs easier using DAO than using SSIS.

Thank you. I appreciate your answer but I am not an Access developer. I have been asked to use SSIS. Cheers.

If the fields in the Access tables don't match, how are you going to map them? I had to do this a long time ago... pretty serious hassle. You could create a "dictionary" of sorts of the field names in the source databases, and then write the unique values to a table and then add another column so you get (SourceColumnName, DestinationColumnName) pairs. Then you'd connect to a new Access "backend" database, loop of the the fields in the table you need, and create the insert statement from the value pairs, and then execute the dynamic SQL. Then go to the next database. You can loop over the directory using DIR etc. (Or use a FileSystemObject).

Thanks but I probably didn't explain myself well enough. I have the same tables in each of the Access database files but there are many of these same Access files in backlog and coming in new. I want to loop through them in the folder then import each. I am getting the feeling that no one does this with SSIS? That surprises me a lot because I thought that would be a common situation. I want to use Access as the file source only but not do development with it. Maybe I can try asking, say, PragmaticWorks (Task Factory SSIS extensions) or CozyROC, if they have something for this or can point me to how to get started doing it. or, maybe they'll say, "not possible without coding in VBA".
I really do appreciate all your responses and I'm sorry I can't do a VBA/DOS batch solution.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search