Import and Export Wizard in SQL Server

Import and Export Wizard in SQL Server

There are many cases where we have to import data from one source system to other. Like Oracle to SQL Server, Flat Files to SQL Server, Excel work sheets to SQL Server, etc.

If we have to import data from flat files (Delimited or Fixed Width or csv) and load into the SQL Server table, we can take the help of stored procedures or utilities. Importing data from the files using stored procedures is a time consuming task. If this is a kind of occasional activity, stored approach is not a recommended one.

If we consider time to develop the job, SQL Server has a wizard (Import and export data) through which we can import the data from files into SQL Server database tables.

Following are the screen shots to import data from flat files to SQL Server Table.

• Open the wizard : (Run à Microsoft SQL Server 2012 à Import and Export Data)

Here is the wizard - 

• Click next to choose the data Source. Select Flat File Source as ‘Data Source’.

3

•  Select the file to import

• Here is the place where we set the properties of the file. Following are the things we can set in this page for the entire file.
•   Code page
• Text qualifier ( For example - If the string values are maintained with double quotes in the file, we have to give “ (Double Quotes) as the value)
•  Setting the Row delimiters 
• From which line of the file SQL Server has to read.
• Whether the first line of the file to be treated as column names or no if at all we have the column names in the first row.

• If we have to set the properties at column level, then we have to go to columns properties and set them.   

• Column properties like column width, column Delimiter, data types, etc can be set in the advanced properties option.

• In the preview, we can preview the flat file data after setting all the properties to make sure everything is fine.

• Click next to select the destination data source. Since we are going to import flat file data to a SQL Server table, Select SQL Server Driver from the destination drop down list.

• Give appropriate credentials and select the database name to where we have to load data .

• Select the destination table if it is available in the database and proceed to the next page.

• If the table does not exist, we can still create the table with the Edit SQL button. Here we can see the default CREATE script SQL Server is creating, or we can even modify the default script SQL Server is creating.

• Check the mapping of columns between source and destination. By default it will map the columns based on the name of the columns. SQL Server will map the columns, if the source and target columns are same. Otherwise, we have to manually create the mappings.  Click next to go the save and run the package.

• Click next to review option we have chosen.

• We can still go back to the previous pages to change the properties of this ETL. If all are fine as per the requirements, Click Finish to run the package.

This is my first post in this blog. Please give comments and suggestions for the improvement in sharing my knowledge in a better way.