SQL Server Import & Export Wizard

Sql server import and export wizard is one of the several Sql Server Integration Services (SSIS) building tools. It can be accessed from DTSWizard.exe, Sql Server Management Studio (SSMS) and Business Intelligence Devolopment Studio (BIDS).

Export data from sql server to an Excel file:

First we want to run the Sql Server Import & Export Wizard. To do that go to run and type DTSWizard.exe and press ok.Then it will open.

Click next. Then it will open another window.

In here specify the data source which the data come from. I chose SQL Server Native Client which is the sql server. Then choose what is the authentication and the database and click next. It will redirect to another window.

Then choose the destination which the data goes to. In here i chose the employeeExcel.xlsx file. Next choose excel version of the file. There is a check box called first row has column names. If we tick it then the first row displayed column names. Click next.

In this phase we have to choose one of two options. First one is 'copy data from one or more tables or views'. If we tick it we can directly select tables or views that data come from. Second one is 'Write a query to specify the data to transfer'. If we tick it we can write a sql query and extract data. First we look at first choice.

This screen displays the source and the destination we chose. Also we can specify the destination work sheet and we can map parameters by click 'Edit Mapping' button. We can also preview the extracted data by clicking the 'Preview' button.

In this phase we can tell run the package immediatly and we can save this package in a sql server or file system.

Here i save the package in my file system and i chose 'Do Not Save Sensitive Data' for package protection level because i dont want to save any sensitive data.

Then name the package and give the description. Specify the location to save the package and click next.

Then it will give us the report that what we have done. If it is ok then we can complete the process.

It will complete the process successfully and we can check whether the data copied successfully by opening the excel file.

Without choosing directly, what are the destination tables or views we can execute a sql query.

Choose 'Write a Query To Specify The Data To Transfer'.

And write a query as u wish. We can select brows a sql file that already wrote also by clicking the Brows button. Here i select the only name and age columns from Employee table. I don't want id column here. After write the query check whether the query is valid by pressing the Parse button.

The sql query is valid.

Then promt to the window that we can select the destination sheet. After specify a sheet name pressEdit Mapping button to map the data.

Here i got woning. It told there is a problem with age column type. We don't want to worry about it. Put the tick to convert and we can proceed.

The execution complete successfully.

The data will be successfully added to the excel file without id column.

In this post i demonstrated how to export data from the sql server to an excel file. Import data process also similar to this. We can give excel file, flat file or any other data source and sql server as the destination. Then like this we can import data to the database.