How to Export data from SQL Server to Excel Using SSIS
Submitted by manjunathcbhat on Thu, 07/10/2014 - 10:05
- Open SQL Server Business Intelligence Development Studio (BIDS). Click on File a New a Project.
- In New Project Screen, Select Integration Services Project, Provide a Name for Project, select a location to place your solution and click ok.
- You should now see a Solution being opened and a default package.dtsx created. Now rename this file to some meaningful name. Once you rename you will get a prompt asking do you want to rename the package object as well? Click Yes
- Now, first off all we want a Data Flow task to carry on our excel export flow. From tool bar drag and drop the Data Flow Task on to our Package and rename it as DFT_ExportExcel. Your screen should look somewhat below now.
- Now double click on our Newly Dropped Data Flow task. Now we want a Data Source and a Destination. You can observe now different tasks have been shown in Tool Bar. Drag and Drop OLE DB Source Task from Data Flow Sources Tab and Excel Destination from Data Flow Destinations Tab. Rename it to some meaningful name. Your screen should look something like below now, ignore the red X(error mark) for now. This will be corrected automatically in coming steps. This is because we have not yet created any connection manager.
- Right Click on Connection Managers window at bottom and select New OLE DB Connection.
- Configure OLE DB Connection Manager window appears, Now select New under this screen at right bottom corner.
- Under Connection Manager Screen, provide the Server Name and Select or Enter a Database Name from which you want to export the data to Excel. Click on Test connection and click on Test connection Succeeded. Click Ok and again click ok. Now You can see a new connection manager is created.
- Now Double click on OLE DB Source Task, connection will automatically be taken. Now you have many options. You can directly get data from a table or view, from SQL Command. I will select SQL Command as I will take only few columns to export for sample. Now write your SQL Query in SQL Command Text area. Your screen should look similar now. Optionally you can preview data by clicking preview button or columns under Columns tab. Now click ok.
- Now double click on Excel Destination Task. Since we do not have any Excel Connection Manger, Click on New.
- Select a Path for Output Excel File in Excel Connection Manager and click ok.
- Select Data Access Mode as Table or View and in Name of ExcelSheet, since we don't have any created, Click on New. Automatically Create Table scripts pops up and click ok. Click Ok in New Pop up.
- Now select MyExcel_Destination$ under name of the excelsheet.
- Click on Mapping Tabs to see columns and click ok.
- Now Go to Debug Menu and Click Start Debugging.
- The results are exported and the DFT_ExportExcel turns green.
- Now if you go to location where you created excel manager you can see your excel file with data.
Hope the article was useful