SQL Server Import and Export Wizard in SSIS – Part 1

15
3194

Let’s get started with SSIS import and export wizard which is one of the simplest ways to implement your first SQL Server Integration Services (SSIS) package. It is very useful as it provides the easiest method to move data from sources like Oracle, DB2, SQL Server, Excel, and text files to any destination.

Implementing Import and Export Wizard can be done by any of the following ways you wish to:-

  1. Open SQL Server Management Studio (SSMS). After connecting to the database engine, Right-click the database whose data you need to import from or export data to SSMS and select Tasks and proceed next with selecting Import Data (or Export Data based on what task you’re performing).
  2. In Business Intelligence Development Studio, right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
  3. Another common way to open it is from the Start menu under SQL Server 2012, where the option is  called as Import and Export Data.
  4. The last way to open the wizard is by typing dtswizard.exe at the command line or Run prompt.

Let’s start implementing this import and export wizard with the 1st way. All the methods will show you the same wizard so you could try any of these.

Below are the values of a Flat file (text file) which we need to load on SQL Server Table.

Name,Age,Location

Sanjeev,23,Chicago

Vijay,23,Florida

Garima,25,Scotland

Idrisha,21,Paris

Bhawan,23,Canada

STEPS :-

STEP 1. Open SQL Server Management Studio (SSMS) and connect to the database engine. Go to Databases, right-click your database and move your cursor to Tasks option and click Import Data.

task_import_data

STEP 2. This will open welcome screen with brief description about the import and export wizard. If you do not wish to see this welcome screen in future, just check the checkbox placed at bottom and click Next to specify Source connection.

welcome

STEP 3. In this step, we need to select Data source from which to copy data. We are given with many options to choose data source varying from Excel source, Access source, Flat file source, OLEDB, etc. You need to choose appropriate source as per your need as in this case our data source will be Flat file source. There will be 4 tabs named General, Columns, Advanced, Preview respectively having fields which we need to specify according to our requirement.

In General tab, you will encounter the below fields –

General_tab

  • Data Source – Choose Flat file source as our file is simple Text file.
  • File name – Click on Browse button and select the file whose data you want to import to the SQL Table.
  • Locale and Code page – If you are working in a different locale then set it else remain it as default.
  • Format – Depending on the format of your input data, you could select Delimited or Fixed Width File. I am using a comma delimited file hence selected Delimited here.
  • Text Qualifier, Header row delimiter, Header rows to skip – This wizard will automatically specify all these fields by default after reading your file, so mostly you do not need to change them. But if you wish you can make changes as per your need.

Note:- Tick the checkbox at bottom if you need to have column names in the first data row. I am checking it here because we have Name,Age,Location as our 1st field. If we will neglect to check it then this will be considered as data records.

Now, go to next Columns tab, fields you will have are –

column_tab

  • Data Source – Do not make changes with this as you have specified it previously.
  • Row Delimiter – It is set to {CR}{LF} by default. CR is for Carriage return while LF is line feed terms derived from olden day’s typewriter. UNIX uses just LF where as Windows use both.
  • Column Delimiter – In our case, Wizard will automatically select comma {,} as column delimiter. In other cases, you can change it to colon {:}, tab {t}, etc. as per your need from the Advanced tab.

advanced_tab

Then comes the Advanced Tab, having list of columns the file contains. We can configure the properties of each column like data type( By default it  is –  string [DT_STR] ),Text qualified, Column delimiter, etc.

Note: Had we not checked the box stating “Column name in the first data row” we would not get the column as you see now names.

After specifying the required fields, you can view the file from the Preview tab.

preview_tab

 STEP 4. Once we are confirmed that all the specified values for the columns are well mapped, click Next.

STEP 5.  Now, the only thing left is to provide the destination so as to copy our data from the Flat file  to the database.

destination

  • Let the Destination be set as default “SQL Server Native Client 10.0”.
  • Specify Server name as localhost (you can also use period {.} which represents localhost). In case you have a SQL server instance like i am having then  you would need to put as <SERVERNAME\INSTANCENAME>.
  • Select the Authentication option from Windows Authentication Or SQL Server Authentication (usually we choose Windows Authentication).
  • Go to New and select the database where you need to import data of your Flat file. You can also map columns using Edit Mapping button as shown below in the snapshot.
  • Click Next and select source tables and views and proceed.

mapping

Congrats !!! Primary setting for our 1st SSIS package is completed. On Save and Run Package Screen, either we can run the package immediately or what we can save it for later execution.

 save_run

 STEP 6. Saving a package is always good to schedule it for later execution.

  • Lets save SSIS package on File system and choose “Do not save Sensitive data” from Package Protection Level Drop down.
  • Click Next and specify the basic fields like Name, Description and Path where we need to Save SSIS Package.
  • On clicking the Next button, you see that the package being validated for all the setting and finally it is saved. It would have run if we had chosen the option of Run immediately.

save

Our package is stored with .dtsx extension at the specified path. You can go the path and check the file. That’s how we export data from a Flat file source to SQL Server Table.

Verify

We will implement the Export data part in our next part. I will really appreciate your valuable comments if this post helped you.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (28 votes cast)
VN:F [1.9.22_1171]
Rating: +25 (from 25 votes)
SQL Server Import and Export Wizard in SSIS - Part 1, 9.9 out of 10 based on 28 ratings
  • Aakash

    Nice tutorial to get started with SSIS Import and Export Wizard… Can we do it for multiple files also, if we wish… Good luck

    • Chander Sharma

      Thanks for appreciating my post. Yes Aakash we can do it for multiple files. There are many ways like Bulk Insert or by using Multiflat file connection managers etc. I will surely include this in my tutorials later. So, keep in touch. 🙂

  • Bhawandeep Singh

    Very nice and step wise illustration of import-export wizard .
    Looking forward for some more related walk through , Good work 🙂

    • Chander Sharma

      Sure man, will post more on SSIS. It’s really interesting tool to work on. Keep sharing and posting your valuable Comments. 🙂

  • Ankita Srivastava

    it s a very good site…

  • Descriptive and well explained via examples tutorial for the import and export wizard……really useful…..loooking forward for more chain topics……..Best Of Luck..

  • Pingback: SQL Server Import Export Wizard – SSIS Part 2 | pHpRing()

  • Hi my loved one! I wish to say that this article is awesome, great written and come with almost all significant infos. I would like to look more posts like this .

    • Hello, Thanks alot for your encouraging words… Will cover other SSIS features in my next post… Any idea regarding topic you want to give me? Also,if you want to contribute any article. you are most welcome pal.

  • Great post !! well organized and easy to understand. 🙂 Will be of great use to all the people who are willing to learn SSIS. Way to go bro 🙂

    • chander sharma

      Thanks sanjeev. You are the inspiration bro .Will love to hear from you as a guest post…

  • Dirmpisee

    Spot on with this write-up, I genuinely think this website wants a lot more consideration. I’ll probably be again to read much more, thanks for that information.

  • Divina Chacko

    I must thank you for the efforts you have put in writing this blog. I really hope to view the same high-grade content by you later on as well. In fact, your creative writing abilities has motivated me to get my own site now 😉

  • Really useful information for the beginners .Go Ahead.good work.

    • Hi Hanefa,
      Thanks a lot for taking time to comment encouraging lines.