SQL Server Import and Export Wizard in SSIS – Part 2

5
1569

Earlier we discussed how to Import data from a Flat File Data source and to load it into MS SQL Server Table. That was very easy to implement and same is the case in Export Data Wizard. We can go ahead by selecting any of the ways I mentioned earlier in my Import Wizard post (If you want to again see them, Click Here -> SQL Server Import Wizard ). I am going by the 1st way i.e Using SQL Server Management Studio.

You can Export billions of data with 1 click using this Export Wizard feature provided in SSIS. Let’s take some data to get started. We created a database named chander (like to get popular, that’s why used my name), containing some data in the form of rows and columns as shown in the image below. Now, lets see how can we export this table to a Simple Text file.

Verify

STEP 1. Open SQL Server Management Studio (SSMS) and Right click on the database whose data you need to Export (say in our case lets select database named chander).Go to Tasks option and select Export Data. This will launch Import Export Wizard screen.

export

STEP 2. When the Microsoft Wizard appears, Click Next to begin and specify the Source connection. These are the basic steps which we performed earlier also while importing data.

STEP 3. Since you had opened the wizard from SQL Server Management Studio by selecting Export Data option, this screen is pre-populated. Now, specify from where your data is coming in the Data source dropdown box. Once, you choose your Data source, other options can vary as per your requirement. According to this example, I will choose “SQL Server Native Client” as Data source. Make sure the Server name references your database server(In our case,it’s localhost), Authentication is set to Use Windows Authentication and select your database which you need to export from Database dropdown(as in this case I am choosing chander as my database). Click Next.

data_source

STEP 4. Now,you will proceed to Choose a Destination Screen to specify where you need to copy data. Go ahead with the following steps :-

  • In the Destination dropdown list, choose Flat File Destination as we are exporting data of our database to a Flat file(Simple Text file).
  • Click on Browse button (under File name option) to navigate to the path where you want to store the your Flat file.
  • Other fields will be set by default like Locale is English (United States) , Code Page is 1252, Format is delimited and Text qualifier is none. But you will have an option to set them as you need. Click Next  and select Copy data from one or more tables or views and proceed ahead.

data_destination

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.

STEP 5. Clicking Next will lead to Configure Flat File Destination Screen. You will see some fields which needs to be specified to configure the destination.

  • Source table or view :- Choose your source from the Dropdown list box.
  • Row and Column Delimiter :- Set Row delimiter as {CR}{LF} and Column delimiter as Tab {t}.

configure_destination

You can preview your data from the Preview button and also can edit columns mappings from Edit Mapping Button. Click Next.

Step 6. On the Save & Run Package screen, you can Run Immediately without saving or can Save SSIS Package by clicking the respective check boxes. If you wish to Save it for later execution then Select File System as the option to save SSIS Package to. Click Next.

save_run

STEP 7. On the Save SSIS Package, enter Package name, description and set File name. Save the package by Clicking Next.

save

STEP 8. Review the details on the Complete Wizard screen and click Finish. The package will be saved to the location we specified and will export the Customers data from SQL Server Table to Customer.txt Flat file.

STEP 9. Let’s verify whether we implemented it properly or just wasted our time specifying fields. Go the specified path where you saved it. Open the Customers.txt file and review the data.

verify

Great !!! We implemented it. So, you can see how easy it is to export data using SSIS Import and Export Wizard.  Add the package to an SSIS Project and review the package created by Import Export Wizard.

So, this completes the implementation of Import and Export Wizard. I hope this helped you in understanding some of your concepts of SSIS. I appreciate the time you dedicated for reading this post and your comments shall encourage me to write more posts. We will reach to the other functionalities of SSIS in our next posts.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.5/10 (26 votes cast)
VN:F [1.9.22_1171]
Rating: +22 (from 24 votes)
SQL Server Import and Export Wizard in SSIS - Part 2, 9.5 out of 10 based on 26 ratings
  • prince

    Very useful document, helped me to import data..

  • prince

    SSIS packages are crucial for data migration. Thanks for it.

  • Aakash

    Easy to undersyand and implement… Thnxxx for sharing..

  • idrisha

    Hi Chander,
    I have read your posts on SSIS. These are really helpful to learn how we should implement and with examples its fun to work. SSIS is really good tool for integration services.

  • TalAntini

    This honestly answered my problem, thank you!