locked
Execute SQL Task vs Data Flow Task for Stored Procedure RRS feed

  • Question

  • I have a few SQL queries which I have turned into stored procedures that are a part of my ETL run. These queries does not need any manipulation using SSIS tools. All the same, these queries are supposed to be part of the chain in the sequence of events in my ETL framework. The queries in question simply pull data from source to destination tables and have in-built business logic specified within them. My question pertains to performance and maintenance. Which is better to use in BIDS for such kind of queries – Execute SQL Task or Data Flow Task? Is there any performance advantages or otherwise of one over the other?

    To me, each has its own strength:

    • Execute SQL Task – I can define/specify in the properties that the query which I am using “IsStoredProcedure”. I believe there is an advantage in specifying this which I am not sure about.
    • Data Flow Task – using this task will enable me to use Row Count component if I so wish. Besides, I can also use Data Viewers if there is a need.

    Can someone please kindly help me with hints and ideas about which task is best to use under the conditions that I have explained above.

    Kind regards,


    Mpumelelo



    Friday, March 8, 2013 1:12 PM

Answers

  • If I could write my data move entirely using a stored procedure, I would use the stored procedure and call it from the Execute SQL Task.  This works if my stored procedure was copying data from one database/table on my server to a second database/table on the server.

    The Execute SQL Task is going to run entirely on the SQL Server.  Using a Data Flow Task is going to pull data from SQL Server, then put it back into SQL Server.

    You can return a row from your stored procedure that includes a row count.  You can then get the results for the rest of you package http://msdn.microsoft.com/en-us/library/ms141689.aspx


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Marked as answer by Mpumelelo S Friday, March 8, 2013 1:58 PM
    • Edited by Russ Loski Wednesday, July 24, 2013 10:52 PM Fix link
    Friday, March 8, 2013 1:23 PM

All replies

  • If I could write my data move entirely using a stored procedure, I would use the stored procedure and call it from the Execute SQL Task.  This works if my stored procedure was copying data from one database/table on my server to a second database/table on the server.

    The Execute SQL Task is going to run entirely on the SQL Server.  Using a Data Flow Task is going to pull data from SQL Server, then put it back into SQL Server.

    You can return a row from your stored procedure that includes a row count.  You can then get the results for the rest of you package http://msdn.microsoft.com/en-us/library/ms141689.aspx


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com


    • Marked as answer by Mpumelelo S Friday, March 8, 2013 1:58 PM
    • Edited by Russ Loski Wednesday, July 24, 2013 10:52 PM Fix link
    Friday, March 8, 2013 1:23 PM
  • Thank you Russ. Interesting stuff that you pointed out there which I wasn’t sure about. Much appreciated.


    Mpumelelo

    Friday, March 8, 2013 1:58 PM
  • Your link appears to be broken.
    Wednesday, July 24, 2013 10:48 PM
  • Your link appears to be broken.

    Thanks.  I fixed it.  The period was added to the href.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Wednesday, July 24, 2013 10:53 PM