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”.



(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.



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.



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.



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:

MessageBox.Show(“Data is being imported”); (You can write your own message)

5. 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.



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).



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.



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.



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).



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



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).



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.



11. Running an SSIS package

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



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

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



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