Run stored procedure from Execute SQL Task, Output result to Excel
July 30, 2010 Leave a comment
The topic seems very common and obvious and so it is. But when I did it the first time, I found it not straight forward. There are few things that we should keep in mind when we do this. I searched a lot (or “googled” as its commonly called :-)) for this, but it was very difficult to locate. The problem is not just about running sproc successfully, but also that the results should go to an excel file. Paragraphs to follow, will describe how this can be done.
- Open the SQL Server Business Intelligence Development Studio and create a new project.
- In the Control Flow tab, drag a Data Flow Task. Double click it to go to Data Flow tab
- In the Data Flow tab, drag an OLE DB Source Task. Double click the OLE DB Source and provide connection in the Connection Manager.
- In the Data Access Mode, select “SQL Command” since we are going to run a stored procedure.
- In the SQL command text area, write the command to execute the stored procedure – EXEC GetEmployees. Check the preview and columns to see everything looks fine. Click OK.
- Drag an Excel Destination Task. Double click and provide connection manager to an excel file.
- In the “Name of the Excel Sheet” drop down, since we do not have the excel file already, click on “New”. A structure for the excel file will be displayed. You might optionally change the name of the table.
- Check the Column Mappings in the “mappings” section. Click OK.
The package is now ready. But there are some important points that need to be checked in order to run this package successfully.
Very Important points to check –
- Ensure that the stored procedure that is being used does NOT reference any temporary tables.
- The stored procedure should not have any PRINT statements.
- Make sure to include SET NOCOUNT ON in the stored procedure.
- When the package is deployed on a different server, ensure the excel file template is present at the destination.
Hope this post helps. Thanks for reading!