Run stored procedure from Execute SQL Task, Output result to Excel

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 – 

  1. Ensure that the stored procedure that is being used does NOT reference any temporary tables.
  2. The stored procedure should not have any PRINT statements.
  3. Make sure to include SET NOCOUNT ON in the stored procedure.
  4. 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!

~Manoj.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: