March 3, 2018 at 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.
March 3, 2018 at 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.
March 3, 2018 at 9:23 pm
pietlinden - Saturday, March 3, 2018 5:59 PMYou 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.
March 4, 2018 at 5:30 pm
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.
March 4, 2018 at 8:03 pm
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.
March 8, 2018 at 10:00 am
pharmkittie - Saturday, March 3, 2018 1:05 PMI'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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 8, 2018 at 10:18 am
sgmunson - Thursday, March 8, 2018 10:00 AMpharmkittie - Saturday, March 3, 2018 1:05 PMn 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..
March 8, 2018 at 11:22 am
"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?
March 8, 2018 at 6:13 pm
pharmkittie - Saturday, March 3, 2018 9:23 PMpietlinden - Saturday, March 3, 2018 5:59 PMYou 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).
March 8, 2018 at 6:59 pm
pietlinden - Thursday, March 8, 2018 6:13 PMpharmkittie - Saturday, March 3, 2018 9:23 PMpietlinden - Saturday, March 3, 2018 5:59 PMYou 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.
March 8, 2018 at 9:29 pm
Oh, now I get it! (One day, I swear I'm gonna learn how to read!)
Basically, you need two things to do this - a ForEach File Loop, and an ExecuteSQL Task. Andy Leonard has an SSIS Stairway on here. The ExecuteSQL task is in the second(?) lesson.
if you like videos, WiseOwl has a pretty good one that does most of what you want (he's importing some text files in a directory), but doesn't cover the ExecuteSQL Task. Between the two of them, you should be able to figure it out.
Sorry about confusing the hell out of you.
Pieter
March 9, 2018 at 6:05 pm
Thanks pietlinden! I will use these resources and if I get it all done maybe I can post what I did in case anyone else is searching for this.
March 13, 2018 at 1:44 pm
pietlinden - Thursday, March 8, 2018 11:22 AM"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?
Well, one thing is that you would then need MS Access actually installed on the server where the SSIS package runs. Another might be that once you do that, you could probably then use a For Each loop container to go through each Access DB filename and then use that info to change a connection manager file name for a connection to the Access database, and thus get them imported one at a time. However, I would then have to question why use MS Access when that could be really slow with a high enough volume of files to process. Question is, what business logic drives the number of files you have to process? Is there, for example, a single file for each business client that submits information? And who will have to fix this when it breaks, for whatever reason? Some things are actually less expensive to do manually by hiring a person at a low level and training them to use SSMA, and making them responsible for fixing it when it breaks. Key is hiring the right smart person. Fixing the problems you could end up with after automating this process could be worse than having a human do it in the first place. Just some things to think about....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply