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

CloudFronts

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

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more