Creating simple SSIS package using SQL Server Import Export Wizard

SSIS (SQL Server Integration Services) is excellent component comes along with SQL Server. SSIS is used to perform data migration task. This is also called as ETL (Extraction, Transformation and Loading) tool. In Data warehousing applications, SSIS is used to perform various operations like importing/exporting data from different sources, perform transformation on data, doing calculation etc.

To start with SSIS, Business Intelligence Development Studio must be installed on your machine and make sure that SQL Server Integration Service is running (Start->Run-> services.msc). After that you need to create SSIS package first. Package is collection of control flow elements, data flow elements, events handlers, variables and configurations. Package is unit of work which can be debugged, executed and saved.

In this post, I will explain how to create simple SSIS package using import export wizard. SSIS has many other features which I will explain in my later posts.

Below are the steps and screenshots to create SSIS package using Import Export wizard.

1. Open Import Export Wizard from Start -> SQL Server -> Import Export Data or Open SQL Server Management Studio and write click on database from which you want to export/import data select task and Export Data.

2. SQL Server Import and Export wizard opens

3. Select source of data. (In below image I have selected SQL Server Data Source and AdventureWorks2008R2 database from where I want to export)

4. Select destination (In below image I have selected Flat File as destination there are many other options available you can select as per your requirement. You need to provide flat file name and if you want to see column name on first row check below displayed checkbox.).

5. Select Table (you can select one or more table to export data or write your own query)

  

6. Select Table from where you want to export data with row delimiter and column delimeter.

Click on Edit Mappings button to select which column data you want to export at your destination file.

Click on preview to see your data

7. Click on Save SSIS Package and File System.

8. Specify the location and name where you want to save SSIS package in local drive.

9. Verify the choices made in wizard and click on finish.

10. Package will start executing and after completion of execution check where package executed successfully or fails.

As per above image, package executed successfully and 504 rows transferred to flat file. See below image which shows column name in first row and below comma separated data.

The similar way you can import data from any source to any destination using Import and Export wizard.

Let’s have a look on created SSIS package. SSIS package has .dtsx extension. In above exercise we have created package named ProductPackage.dtsx. Double clicked on this file and it will open in Visual Studio see below screenshot.

This image shows one data flow task created on control flow tab.

On double click that task you can reach to Data Flow tab and it shows how data transfers from source to destinations.

You can also watch below video which demonstrate above mentioned all the steps and explains how create simple SSIS package using import export wizard.

Hope you liked this post. Please leave your feedback in comments section.