Version of SSMS required to import data and append to existing table

Hello Experts

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!

Yours

Hopeful Kiwi
Mark DalleyInformation AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
I don't think it has got anything to do with the version of SSMS that you are using. Here's what I think is happening.

Let's say, source database has tables tblA-tblZ and the Destination has tables tbl1-tbl10. You would like to import data from source table tblA to destination tbl1.

When you use the import wizard, notice that in the "Select Source Tables and Views" screen, the destination column would also automatically read "tblA" when you select tblA in the source column. In this case there would a little star on the icon (indicating that a new table will be created). When you do "Edit Mappings" in this case, the Append and Delete rows options will be disabled (because the table "tblA" does not exist in the destination).

All you need to do is change the mapping to the appropriate tbl1 in the destination column and then click on Edit Mappings. If the table exists in the destination database, you would see that "Create Table" is now disabled whereas the other 2 are enabled.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark DalleyInformation AnalystAuthor Commented:
Hi Nakul

You are spot on. I knew it had to be something obvious!

Before I leave this, though, what is a good way of automating something like this? I don't want to have to use the Wizard every time.

Hopeful Kiwi
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
At the end of the wizard, you get an option to save the package (which would work if you would want to do the exact same process with the same source & destination again). If the source & desitnation are dynamic, a good option would be to create a SSIS package and schedule it. Creating a custom package will also allow you to migrate data from multiple sources to multiple destinations with a very high degree of customization and control.
0
Mark DalleyInformation AnalystAuthor Commented:
Not with this version of SSMS...At the end I get the message:
In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

My team leader has a different version, which offers Integration Services and all the other goodies, not just the database engine.

I am currently waiting for IT to give me a "proper" version of SSMS (i.e. one with Integration Services), and hopefully they will get around to it sometime this week.  Is it possible to download from somewhere?

Hopeful Kiwi
0
Mark DalleyInformation AnalystAuthor Commented:
Hi all

I have *finally* got a proper version of SSMS. I know nothing of its origin except that IT installed it while I was away. If I can get hold of them (difficult) I am sure they can enlighten me.

Thanks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.