16 Jan’17

BIMoving Data from SQL Server to Excel Using SSIS

In this blog article, we will explain how to move data from SQL server to Excel file using SQL Server Integration Services (SSIS).

For moving data from SQL Server to Excel you need to create package, to import data from SQL Server to Excel.

Step 1: – Create a Project

You need to create a project for moving data from SQL Server to Excel. For this go to visual studio-> File-> New-> Project

Once you click on project then choose a business intelligence option to create SSIS project. Then select Integration Services.

And then Click on Integration Server Project. Specify name to the project and then click on OK.

Step 2: – Create a Data Flow Task

We need to add a data Flow task to control flow tab of your package.

After adding the data Flow task double click on it will open the Data flow tab.

Step 3: – Creating the SQL Server Source

Our aim is to move data from SQL Server to Excel. So here our source is SQL Server and Destination is Excel

So, drag and drop the OLE DB Source component from the Source then right click on that component then click on edit. Below window will open.

Then click on new

Click on new

Then select your Server name, Authentication type.

Select your database name means from which database you retrieving the data.

Then click on Test connection to verify that our connection is right or not.

And then click on OK. Again, click on OK.

Then select the table from Specified database.

You can also verify data by clicking on preview button then click on OK.

Step 4: – Creating the Excel Destination

Our destination is to put the data in excel file.

So, drag and drop the Excel destination component from the Destination.

Then click on OLE DB Source component and then drag blue line and drop on Excel destination.

then right click on that component then click on edit. Below window will open.

Then click on new then set the path where you want to create an excel file.

Click on OK.

Then click on another new button to select the excel sheet name.

Click on ok -> ok

Then click on drop down bar choose excel file then click on mapping.

and then OK.

Here, your package is ready execute. Before executing the package verify the source and destination. In destination, there created one excel file with name that you specified in earlier in excel destination with no data.

For execute a package, go to Solution explorer ->under the project name -> SSIS Packages -> Package.dtsx.

Then right click on that .dtsx and the execute package.

it will show the number of rows inserted in Destination i.e. in excel file. And then check the destination. All the data is integrated in destination.

Written by

Team Member


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Dynamics 365 Client Diagnostics

    15 March’ 2018

    Main cause of Performance Issues: Bandwidth and latency are the primary characteristics which affects the performance o...

    Read more
  • Closing Blind Shift in MPOS and CPOS in Dynamics 365 for Retail

    15 March’ 2018

    Manual For closing Blind shift in POS: Blind shifts terminate the currently ongoing shift and when logged in a new shif...

    Read more
  • Persistent Filters in the Power BI Service

    13 March’ 2018

    Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filt...

    Read more
  • Cause and Solution for Scribe MSMQ not receiving Message from AX

    10 March’ 2018

    Issue: Microsoft Message Queuing (MSMQ) service running on Server might be unable to receive messages. Therefore, messa...

    Read more
  • User Privileges on SharePoint – D365 Document Integration

    10 March’ 2018

    Overview: Often, we wonder what could be wrong where we see a generic error on Documents in Dynamics 365. Something lik...

    Read more