How do I change the default server / instance name when using SQL Server Native Client 11 in the Import and Export Wizard?

Michael Ballard 1

I have 3 SQL Server instances on my PC (SQL2012, SQL2014 & SQL2016). The services for 12 & 14 are disabled. Currently, when I use the Import and Export Wizard, I want it to connect to an instance on a network server (STX-DEVSQL\SQL2017). When I start the Import and Export Wizard and select SQL Server Native Client 11.0, it always assumes that I want to connect to my local 2012 instance:

How can I change this?

Yitzhak Khabinsky 25,851 Reputation points

2020-11-03T20:05:18.79+00:00

The wizard cannot read out mind, that's why it selects a local instance by default.
You can just type instance name by hand.
Michael Ballard 1 Reputation point

2020-11-03T20:09:45.683+00:00

I understand that it's not a mind reader. Even so, it's looking up that local instance somewhere.
I'm tired of typing the desired server/instance every time. I want it to remember what I used last time or provide a way to specify the default.

2 answers

Monalv-MSFT 5,896 Reputation points

2020-11-04T03:33:01.727+00:00
Hi @Michael Ballard ,

It is better for you to enter the name of the server manually.

Or we can expand the combo box but it may take a long time to list all available servers.

Please refer to How to import/export data to SQL Server using the SQL Server Import and Export Wizard.

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues in October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Michael Ballard 1 Reputation point

2020-11-04T17:10:58.937+00:00

Many other tools 'remember' the last value used. That would be my preferred solution. Second choice, provide some sort of configuration setting (perhaps in the system registry) where I can specify my preferred server. Opening the listbox is not useful because of the size of our network, the number of servers and the fact that I work remotely. If it has to show a local instance, at least show an active instance. My 12 & 14 instances have been turned off for more than a year.
Please sign in to rate this answer.
Yitzhak Khabinsky 25,851 Reputation points

2020-11-04T17:14:59.49+00:00

IMO, you have some unrealistic expectations.
The current Import and Export Wizard behavior is by design.

Though you can ask for what you need on the Azure Feedback: sql-server

Monalv-MSFT 5,896 Reputation points

2020-11-12T09:59:47.247+00:00

Hi @Michael Ballard ,

Hope the following link will be helpful:

Import and Export Data with the SQL Server Import and Export Wizard

Best Regards,
Mona

Michael Ballard 1 Reputation point

2020-11-12T17:50:59.427+00:00

I am very familiar with that document but it doesn't help me. For data security reasons, my company insists that I not import data into a local instance of SQL Server. Instead, I should import to a server at the corporate office behind multiple layers of security. Typing "JDI-DEVSQL\SQL2017" once is no big deal. Some days I have to type it 20+ times and that gets annoying. I'm asking to an enhancement to the tool to either remember the last server used (maybe an MRU list like SSMS) or let me specify a default server or instance.

Jeffrey Williams 1,891 Reputation points

2020-11-15T17:41:05.707+00:00

Instead of using the import/export wizard - save the packages as SSIS packages, then edit/modify them using SSDT. This way, once you have a package set up - all you have to do is open the package - change the source file (assuming you are importing the same data) - and execute the package.

I would recommend that you create a project in SSDT - build separate packages for each file type - and then use that to import/export the data. Once you have that process in place...the next step would be to look at automating it so you don't have to manually perform these processes 20+ times a day.

Michael Ballard 1 Reputation point

2020-11-16T17:17:57.007+00:00

If I was reimporting the same data over and over or if the spreadsheets had the same format from one to the next, I would certainly use SSIS.
That's not what I receive. Usually, the spreadsheets were created by people who are not particularly careful about formatting them the same way.
If the wizard would 'remember' the last server / instance I connected to or let me set a default, it would be a small help that would make my job just a little bit easier.

Jeffrey Williams 1,891 Reputation points

2020-11-16T21:12:21.377+00:00

It is just as easy to setup a new data flow in an SSIS package - as it is to use the import/export wizard. If you create a project - you can then setup your connection as a project connection and define the connection information one time. Create a new package in that project - add a data flow - add a new package file connection and set that up to the new file.

Add a destination using the project connection...in that connection either create a new object or use an existing one (same as you would in the wizard) and map the columns (probably won't be needed - if you are creating new tables each time).

Execute the package in SSDT...

Now you have something that is reusable - if you get the same file...you already have a package ready and it just needs to be adjusted for this process (create a new table in the destination...use existing table, etc...).

Jeffrey Williams 1,891 Reputation points

2020-11-16T21:18:40.67+00:00

Additionally - since you now have this saved - if you get a similar file with just a few differences, you can reset the columns in the source, remove the old/invalid columns in the connector - and update the destination.

Once you get the hang of using SSDT - you will find it is much easier than constantly running the wizard for each new file. Need a new package, copy the package in SSDT and you have a ready made package to be edited and fixed up for the next file. Want to use the same package over - just reset the columns, make a few changes, and execute.

If you are constantly updating the tables to correct the data - you can add a derived column transformation...or conditional split if the data has to go to separate tables...or other transformations so they are done as the data is imported instead of having to rewrite/update SQL code.

Monalv-MSFT 5,896 Reputation points

2020-11-20T09:23:50.307+00:00

Hi @Michael Ballard ,

We can also use the source and destination in Data Flow Task to transfer and load the data.

Best Regards,
Mona
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How do I change the default server / instance name when using SQL Server Native Client 11 in the Import and Export Wizard?

2 answers

Your answer