Converting Access Databases & Queries to SQL Server

[basebalplayr33]

Hey all,

I recently began converting all of our firm's Access databases and Queries to SQL Server due to size/efficiency/etc, and I was hoping someone could recommend a better way for me to complete this process. For each of our clients, we receive monthly or quarterly data files (usually in a Text file) and essentially run this file through a series of 8 different access databases like an assembly line. Each time we receive a text file, this file is imported to a large table in the first Access Database, and a series of about 30 queries is run to format this data and extract the necessary information into databases 2, 3, 4, 5, 6, 7 and 8 which each run a series of about 10-50 queries. I am now taking all of the Tables from this series of access databases, and importing them to SQL Server manually using the Import Export Wizard. Once these tables are all in SQL, I then transcribe all the queries to SQL code (Usually about 250+ queries). Lastly, I set up Delete Queries that delete out last month/quarter's data and bulk inserts that automatically perform the Text File Import. This process works, and once all queries and tables have been moved to SQL, the entire script runs in about 10 minutes, which is beautiful. My issue is that I need to do this same process for roughly 15 other Clients. Is there any way that I can take my series of databases and queries and import everything into SQL without having to manually go through and re-write hundreds of queries each time? Even if there is a program out that there that would help me to accomplish this, I would be willing to purchase it. Thank you all in advance for any help you can provide me with.

 

[basebalplayr33]

One recommendation that I have received elsewhere is to use SSIS. Is this something that would be able to easily help with my problem?

 

[xenou]

as far as I know using the upsizing wizard is the normal way to go.
Example:
https://support.office.com/en-us/ar...g-Wizard-5D74C0DF-C8CD-4867-8D07-E6E759D72924
https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/how/index.htm

Generally, googling MSAcess Upsize will give you many results to check out. Note: SSIS seems like a red herring to me. It would probably allow you to connect to and query an MSAccess database, as well as move data back and forth between Access and SQL server, but I've never heard that it can transform Access databases into SQL Server databases.

 

[basebalplayr33]

as far as I know using the upsizing wizard is the normal way to go.
Example:
https://support.office.com/en-us/ar...g-Wizard-5D74C0DF-C8CD-4867-8D07-E6E759D72924
https://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/how/index.htm

Generally, googling MSAcess Upsize will give you many results to check out. Note: SSIS seems like a red herring to me. It would probably allow you to connect to and query an MSAccess database, as well as move data back and forth between Access and SQL server, but I've never heard that it can transform Access databases into SQL Server databases.

Xenou,

Thank you so much for the quick reply. I did some googling and found out that the Upsizing Wizard was decommissioned in 2013. I am using Access 2016 and SQL SERVER 2016. I did download the SSMA v 7.0 just now. It seemed like it was going to work, but I received the following errors:

- A2SS0041: SQL Server Migration Assistant for Access Error Message: Append query cannot be converted because it is not supported (4) Estimated manual conversion time .8hrs
- A2SS0044: SQL Server Migration Assistant for Access Error Message: Delete query cannot be converted because it is not supported (4) Estimated manual conversion time .8hrs
- A2SS0046: SQL Server Migration Assistant for Access Error Message: Update query cannot be converted because it is not supported (50) Estimated manual conversion time 10 hrs

 

[xenou]

To be honest, I wouldn't expect it to work without at least some errors. Access is in some ways incompatible with sql server and some pieces/parts probably require some extra attention - for starters, it uses some functions that are not in T-SQL such as Nz() and IIf().

 

[ScottR]

It has been some time since I've been involved with this kind of issue but my opinion varies wildly from xenous' so I'll pitch my two penneth worth anyway.

First consideration - access as a linked database. This, as far as I know, is the "standard" way of bringing Access into a SQL server environment. If my memory is correct it works with impressive speed.

Potentially some issues with data conversion (dates especially) but see here for a starter https://support.office.com/en-gb/ar...database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

Second consideration - Would be your somewhat non standard use; ie chaining access databases to export and import text files. This is a bizarre, inefficient and risky setup and sort of contrary to the normal use of a database (why not read directly from tables rather than export text for example???).

You should eliminate this but I completely understand why you've been pointed towards SSIS. If you have flat file structures (including most Excel report type things) that need integration/storage with your production system then SSIS is the way to go and you can automate virtually the entire processes.

You'd normally do this because you have to though and if you're processing all data within a sequence of relational databases then you definitely don't have to

 

[xenou]

I think the question I was answering was "can SSIS convert an MSAccess database to SQL Server database". As far as I know, it there is not a wizard or tool in SSIS that specifically does that.