I need to import a fair amount of data into SQL Server 2008 from various Access 2010 .accdb files.
Up till now I have gone into SSMS and used Database > (db name) > Tasks > Import Data. In the wizard I select the MS Office 12.0 Access Database Engine OLE DB Provider, click Properties, specify the data source, test connection is OK, fine. I then select the destination (SQL Server Native Client 10.0, Windows authentication, and the database name: the defaults are all correct). On the next screen I select "Copy data from one or more tables or views" On the next screen, "Select Source tables and views" I select the table of interest.Up till now, I have created a new temporary table and imported the data into that, which works.
HOWEVER, I now want to APPEND the data to an existing table. When I click on Edit Mappings, sure enough, there are 3 options: Create table (the default), Delete rows in destination table, and append rows to destination table. However, the second two options are greyed out and can't be selected!
How do I enable the option I need? Am I going about this the wrong way, or do I have a wrong version of SSMS, or the wrong version of SQL Server, or is it a rights issue, or what? This ought to be simple, basic ETL.
A note that may or may not be relevant: I cannot select Integration Services in the SSMS Connect window. The only options are "Database Engine" and "SQL Server Compact Edition".
I have tried our local IT support desk with no success. I reckon a couple of people know, but I can never get hold of them, so it's over to collective brilliance of EE!