Importing Table Data From SQL Server to Excel Workbook Using SSIS

I am using Visual Studio 2012. First of all let's learn how to create an SSIS package/project.

1. Creating an SSIS package/project:


Procedure: In Visual Studio go to "File" -> "New" -> "Project..." then select Business Intelligence in installed templates then select Integration Services -> Integration Service Project then provide a meaningful name then click “OK”.

Integration Service Project

(If you're using Visual Studio 2012 and don't find “Business Intelligence” in the installed templates, there is no need to worry, you can download it by just clicking here.

Once you click OK it will create an SSIS package as shown below. You can see the package on the right side top corner of your Visual Studio (in the Solution Explorer), it will also provide you the SSIS toolbox that you can find on the left side of Visual Studio. Finally you'll also be able to see the control flow and Data Flow in your project.

2. SSIS Package/project

We've successfully created an SSIS project/package.

SSIS Package

3. SQL Table Data

Now I've the following data in the SQL Server Database table (table name = empdata) and I want to import this data into an Excel workbook using the SSIS package we created just now.

SQL Table Data

4. Creating Control flow and Data Flow tasks

Control flow is the workflow engine that contains control flow task containers and precedence constraints that manage when tasks are executed.

The Data Flow is related to the transformation of data from the source to the destination.

Note: In this article we are importing data from SQL to an Excel book, so SQL Server becomes our source and Excel workbook becomes the destination.

I will now create two tasks, one is a control flow task and the other is a Data Flow task. Just drag and drop the script task from the common tasks (from the SSIS tool box) because I want to display a message like “Data is being imported “, when our package is executed. And then drag and drop the Data Flow task from the favorites as shown below.

Creating Control flow

Rename the script task to any meaningful name if you want. In this case I am changing it to Display reassuring message (renaming is optional).

After you have added the two tasks to your projects, be sure to link these two tasks with the Green arrow mark (precedence constraint) to ensure that the two tasks execute one after the other.

Now double-click on the script task and it will give you the “script task editor” where you can select the script language and edit the script. Please click on the edit script button to display a message when your package is executing.

Once you click "Edit Script" then click "OK" then you will see a new project with the ScriptMain class. In that class go to the Main method and add the following code:

  1. MessageBox.Show(“Data is being imported”); (You can write your own message)  
5. Script Task Editing

Script Task Editing

Then save the changes and close the window by clicking the "x" symbol on the top-right corner.

Now just double-click on the Data Flow task to import the data from SQL to Excel. Once you double-click on that it will be moved to the Data Flow window from the control flow.

In the Data Flow window you'll have three types of tasks, they are:  
  1. Source tasks
  2. Destination tasks
  3. Transformation tasks

6. Adding Source to Data Flow window

Source is where the data is coming from. In our case we are importing data from SQL Server so just drag and drop the OLE DB source from the other sources template.

Adding Source to Data flow window

7. Creating Source Connection

Now we need to specify the source connection. This can be done by right-clicking on the connection manager (in the Solution Explorer) then select New Connection Manager then select OLE DB (because OLE DB is the best choice for connecting to SQL Server).

Creating Source Connection

Now double-click on the OLE DB source and select the appropriate source table in the OLE DB source editor as shown below. I am selecting EMPDATA table because I want to import employee data to my Excel workbook.

EMPDATA table

8. Adding Destination to Data Flow window:

We have just added the source. Now we should add the destination task (Excel workbook) to the Data Flow window. The destination is where the data is going to.

Destination to Data flow window

9. Creating Destination Connection:

After adding the Excel destination, we should create a destination connection. Right-click on the Connection Manager then select New Connection Manager then select Excel (because Excel is our destination).

New Connection Manager

Once you click Add it will ask you to choose the path to store the Excel work data (Destination).

click Add

Provide any path location to store the data.

After selecting the path click OK and next double-click on the Excel destination. It will take you to the Excel Destination Editor. In the name of the Excel sheet field select New and it will create a table then click "Ok". And now select the name of the Excel sheet table (there will be two sheets with the same name, ignore the one ending with $ symbol).

Excel destination

10. Add OLE DB Source to Excel Destination with the Green arrow link

Finally add the OLE DB source to the Excel destination with the Green arrow link to ensure the data transformation.

Add OLE DB Source

11. Running an SSIS package

Right-click on the package and select execute package.
Or
Click Start.

execute package

12. Verify the Destination Now (Excel sheet to confirm the output)

Its time to observe the destination folder (Excel sheet to verify the result).

confirm the output

As we can observe from the preceding Excel sheet, the data has been imported from SQL Server to our Excel workbook.