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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
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
ASKER
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.
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.
ASKER
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