none
Exporting Stored Procedure Results to Excel

    Question

  • I'm new to Integration Services and going through the learning challenges one often faces when employing unfamiliar technology.  I've created a simple package that executes a parameterized stored procedure and returns the results (multiple rows) into an object variable.  How do I export the results to an XLS file?  Do I have to use a script task to do this? 

     

    Regards,

    Orlanzo

    Friday, November 09, 2007 8:33 PM

Answers

  • Just wanted to clarify a little - instead of an Execute SQL, you should add a Data Flow task to the control flow. Then open the Data Flow, and add a OLE DB Source. Put your stored procedure in that OLE DB Source, and then connect the green arrow from that to an Excel Destination.

     

    The Execute SQL task doesn't actually pipe data anywhere. It can store the data returned from a Select into a variable, but not directly into Excel.

    Saturday, November 10, 2007 3:08 AM
  • The import wizard uses that execute sql task to create the execel file used by the excel destination component in the dataflow. The excel destination component does not create the file for you; the file has to exist when the dataflow task gets executed; otherwise it fails. (here is an example: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html)

     

    I Don't have experience using SP in the OLE Source components; and it looks like you have applied the workarounds I have seen before in this forum; so I cannot offer further help there.

     

    Monday, November 12, 2007 1:38 PM

All replies

  •  

    Orlanzo,

    There is a data flow component called "Excel Destination". Use that to configure the output and the results will be directly placed in the excel file.

    - Pavan

    Friday, November 09, 2007 8:40 PM
  • Pavan,

     

    How do I pass the results (rows returned from stored proc) of the Execute SQL Task to the Excel Destination Task?  The Excel Destination Task gives an error (which I'm googling) stating no available input columns.

     

    Orlanzo

    Friday, November 09, 2007 9:13 PM
  • Click on the Execute SQL Task and drag the green arrow onto the destination.

    Friday, November 09, 2007 9:53 PM
  • Just wanted to clarify a little - instead of an Execute SQL, you should add a Data Flow task to the control flow. Then open the Data Flow, and add a OLE DB Source. Put your stored procedure in that OLE DB Source, and then connect the green arrow from that to an Excel Destination.

     

    The Execute SQL task doesn't actually pipe data anywhere. It can store the data returned from a Select into a variable, but not directly into Excel.

    Saturday, November 10, 2007 3:08 AM
  • Thanks John,

     

    Ok, I was trying to use the variable holding the results of the stored procedure and a variable can't be used as the source for the Excel Destination task.  So, I just need to setup an OLE DB source in the Data Flow mapping the results of the Stored Procedure to Excel Destination.  I'll give it a whirl.  I assume I'd call the stored procedure as I normally would using T-SQL.  Can I reference a package variable as a parameter to the stored procedure?

    Saturday, November 10, 2007 3:37 AM
  • Yes, you can, using the Parameters button. You can also use a variable with an expression defined on it to create the call to the stored proc and append the parameters, and reference that variable from the OLE DB Source.

     

    Saturday, November 10, 2007 4:14 AM
  •  jwelch wrote:
    You can also use a variable with an expression defined on it to create the call to the stored proc and append the parameters, and reference that variable from the OLE DB Source.

     

     

     

    Personally, this is the option I would choose. Declare a variable, set its expression to build the EXEC command you want to execute against the source database, evaluate the expression and execute its output manually in SQL Server Management Studio, and you know it works before you start trying to use it in your data flow. It's a good and reliable time-saving technique.

    Sunday, November 11, 2007 2:05 PM
  • Ok, I created a Data Flow task on the Control Flow pane and added an OLE DB task to it.  The OLE DB was configured to use a SQL Command which called a stored procedure.  The procedure is based on the AdventureWorks sample database, does not utilize temporary tables, and returns a single resultset.  The procedure returns results in Management Studio and when previewed from the OLE DB task.  However, their are not output columns.

     

    I read other posts by users experiencing similar behavior.  In most cases, temporary variables were being used which prevented the OLE DB task from successfully reading the metadata of the procedure.  I set the NOCOUNT and FMTONLY properties within the stored procedure as some had suggested worked for their particular cases.  Those settings had no affect in my case.

     

    Lastly, I used the Import/Export Wizard to create a package.  This worked as expected.  The stored procedure was executed, results returned, and the subsequen XLS file was generated.  The package created by the wizard incorporates an Execute SQL Task on the Control Flow.  Execute SQL Task is followed by a Data Flow task. 

     

    The Data Flow contains an OLE DB Source >> Data Conversion >> Excel Destination.   The OLE DB Source calls the stored procedure as a SQL command i.e. exec <proc name>.  So, what was the Execute SQL Task doing?

     

    I checked the properties of the task and saw the following property settings:

     

    ConnectionType:   EXCEL

    SQLSourceType:  Direct Input

    SQLStatement:    CREATE TABLE `Query` (

    `Category` LongText,
    `Subcategory` LongText,
    `Total Quantity` Long
    )
    GO

     

    Of course, I was curious as to what was occuring here...  I checked the Excel Destination Task and saw the table created above 'Query' was set as the value of the 'Name of the Excell sheet:" property.  Why would this be required and necessary?

     

    The OLE DB Source was configured as I had been using it all along.  It was set to use a SQL Command with the command text calling the stored procedure I'd created previously.  Why are available columns now listed on the Colunns pane?

     

    I tell you this is a bit odd and wouldn't at all seem to be intuitive or necessary.  I would expect to simply call the stored proc in the OLE DB Source which would interpret the results returned by the stored proc, generate the column information, and pass the results on to the Excel Destination Task.

     

    Am I missing something here?

     

    The procedure I'm using with AdventureWorks is:

     

    Create Procedure [dbo].[usp_getInventory]

    AS

    SET FMTONLY OFF

    SET NOCOUNT ON

    Begin

    Declare @myTable As table (

    Category varchar(50),

    Subcategory varchar(50),

    [Total Quantity] int)

     

    Insert Into @myTable

    Select cat.Name 'Category',

    subcat.Name 'Subcategory',

    Sum(inventory.Quantity) 'Total Quantity'

    From Production.ProductCategory cat

    Inner Join Production.ProductSubcategory subcat

    ON cat.ProductCategoryID = subcat.ProductCategoryID

    Inner Join Production.Product product

    ON product.ProductSubcategoryID = subcat.ProductSubcategoryID

    Inner Join Production.ProductInventory inventory

    ON inventory.ProductID = product.ProductID

    Group By cat.Name, subcat.Name

    Order By cat.Name, subcat.Name

    Select Category, Subcategory, [Total Quantity] from @myTable

    End


     

    Sunday, November 11, 2007 9:36 PM
  • The import wizard uses that execute sql task to create the execel file used by the excel destination component in the dataflow. The excel destination component does not create the file for you; the file has to exist when the dataflow task gets executed; otherwise it fails. (here is an example: http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html)

     

    I Don't have experience using SP in the OLE Source components; and it looks like you have applied the workarounds I have seen before in this forum; so I cannot offer further help there.

     

    Monday, November 12, 2007 1:38 PM
  •  

    Thanks for the information Rafael.  I wish it were made clear the Excel Destination Task doesn't create the file.  That would have saved me some time in developing a solution. Additionally, more information regarding how the file could be created using the Execute SQL Task would have also been beneficial.  Although, I'm going to create a template file for regular use.

     

    Again, thanks for your help.  The forumn is been quite beneficial and I'm sure I'll be back again!

    Monday, November 12, 2007 3:04 PM