Extract SSMA mappings and conversions for use with SSIS

Question

I'm fairly certain SSMA (Version 5.0 for Access 2007) creates an SSIS package as part of its work, then executes it. I'd dearly live to get that package out of SSMA to use as a way to periodically "refresh" the SQL Server db while I'm doing the lengthy conversion. Anyone have any insight?

---

Jim

Answer 1

I tried SSMA and found it awkward enough to make me want to write my own SQL management tools. DDL (data definition language) might be the way to go, or ADP (Access Data Project?) for managing SQL Server via the Access front-end. I'm not sure which way to go—personally I'm probably going to start using DDL on SQL Server and then make the transition to .NET (away from MS Access altogether). For now, I'm doing Access migrations with Access 2010 (which has an "SQL Upsizing Wizard" that supports SQL Server 2008 R2.)

---

Matthew Slyman M.A. (Camb.)

Answer 2

Hi Jim,

What is the status of the problem on your side? If you still show any concern, just feel free to let us know.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

It's been around for awhile. I guess I'm going to have to do things the hard way.

---

Jim

Answer 4

Hi Jim,

We are doing the research about your problem. There might be some delay about the response. Appreciate your patience.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hey Jim,

 

Since it sounds like your issue is related to SSMA, I am going to move the thread over to the SQL Server Migration forum and hopefully someone on that forum will have some info they can provide.

 

Best Regards,

Nathan Ost

Microsoft Online Community Support

---

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 6

As far as I know, SSMA is not using SSIS to do the data movement.

What's your requirement, Jim? Looks like you want to sync between Access and the migrated SQL database?

Answer 7

Yeah, I'm doing a long-term (since I'm alone) migration of an app from AccessFE/AccessBE to AccessFE/SQLSrvrBE. If I can sync the 103 tables a couple times a day, I can get others to test FE forms, etc. That way, I don't have to do it all myself.

Using SSIS has been a pain. I used the wizard to set it up, but the wizard doesn't allow for turning off identity update prohibition, and temporarily killing the relationship rules so the data can be uploaded without all that checking (and sequencing....) Building SSIS packages one or two tables at a time is a long (103 tables) time consuming process that SSMA has already done and saved.... 

---

Jim

Answer 8

Hi Jim,

SSIS does not appear to be a component of SSMA.

If SSMA is not giving you the flexibility you need -

You also can migrate data outside of SSMA by using the SQL Server bcp command-line utility or Integration Services. For more information about these tools, see SQL Server Books Online

 

 

 

Answer 9

Hi Jim,

You can save the SSMA For Access project and reuse it whenever required for just migrating the data. 

To automate the process without using User Interface, you can use the Console Application provided with SSMA, to read more about How to use SSMA Console Application

Please refer to SSMA Help provided with the install and check out the section "Working with SSMA for Access Console" 

and also read this article

http://blogs.msdn.com/b/ssma/archive/2010/09/09/performing-database-migration-assessment-using-ssma-console-application.aspx

FYI, This article is written for SSMA for Oracle, but the same concept applies for Access too

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"