none
OLE DB Source Table or View Meta Data

    Question

  • I configured a data flow task with Table or View as the data access mode. The following is a screen shot from SQL Profiler.

    Following command is executed twice.

    SET ROWCOUNT 1
    SELECT * FROM [dbo].[BigTransactionHistory]

    Then the actual query.

    SELECT * FROM [dbo].[BigTransactionHistory]

    I can think that the first query is executed to read the meta data, but why does SSIS have to execute it twice?

    Can anyone please help me answer this?

    Thanks!


    Regards, Samuel Vanga Twitter Blog

    Saturday, April 07, 2012 7:48 PM

Answers

  • Hi, Thank you for the response. However even with DelayValidation set to true, I'm seeing the first query more than once. Also, i noticed the second query (SELECT *...) only once even with DelayValidation set to false.


    Regards, Samuel Vanga Twitter Blog

    what about setting validateExternalMetadata to false on the source component?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Samuel Vanga Monday, April 09, 2012 9:53 PM
    Monday, April 09, 2012 8:22 PM

All replies

  • Here is my understanding (I will let the experts correct me).  The first query gets the metadata.  If the metadata does not match what the SSIS data flow can work with, then you will get a validation error and the full query will not execute.  It saves a wasted call to the database with a Select * with no where clause. SSIS has no clue whether the metadata in the table will work or not.

    However, if you use a SQL command (perhaps especially with column names), then SSIS uses a more efficient method to get the metadata.  http://msbicodehelp.blogspot.com/2011/09/performance-considerations-for-ssis.html


    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

    Sunday, April 08, 2012 1:04 AM
  • Hi Russ, Thanks for the response. The first query to get the meta data is being executed twice. I'm curios why? I do use SQL Command always, but noticed this behavior while testing something else and wanted to know how it works internally. 

    Regards, Samuel Vanga Twitter Blog

    Sunday, April 08, 2012 2:09 AM
  • Do you happen to have two sources in your Data Flow?

    Todd McDermid's Blog Talk to me now on

    Sunday, April 08, 2012 6:43 PM
  • Hi Russ, Thanks for the response. The first query to get the meta data is being executed twice. I'm curios why? I do use SQL Command always, but noticed this behavior while testing something else and wanted to know how it works internally. 

    Regards, Samuel Vanga Twitter Blog

    Not sure but isn't the first the immediately validation that starts when you start the package (which you can bypass by setting DelayValidation to true) and the second is the validation when the data flow starts.

    You could test this by setting DelayValidation to true on the Data Flow Task.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Sunday, April 08, 2012 7:00 PM
  • Do you happen to have two sources in your Data Flow?

    Todd McDermid's Blog Talk to me now on

    Todd - No, i have only one data source.

    Regards, Samuel Vanga Twitter Blog

    Sunday, April 08, 2012 11:58 PM
  • Hi Russ, Thanks for the response. The first query to get the meta data is being executed twice. I'm curios why? I do use SQL Command always, but noticed this behavior while testing something else and wanted to know how it works internally. 


    Regards, Samuel Vanga Twitter Blog

    Not sure but isn't the first the immediately validation that starts when you start the package (which you can bypass by setting DelayValidation to true) and the second is the validation when the data flow starts.

    You could test this by setting DelayValidation to true on the Data Flow Task.


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Hi, Thank you for the response. However even with DelayValidation set to true, I'm seeing the first query more than once. Also, i noticed the second query (SELECT *...) only once even with DelayValidation set to false.


    Regards, Samuel Vanga Twitter Blog

    Monday, April 09, 2012 7:50 PM
  • Does the behavior change if you give your query a field list instead of Select * ?

    Chuck

    Monday, April 09, 2012 7:53 PM
  • Hi, Thank you for the response. However even with DelayValidation set to true, I'm seeing the first query more than once. Also, i noticed the second query (SELECT *...) only once even with DelayValidation set to false.


    Regards, Samuel Vanga Twitter Blog

    what about setting validateExternalMetadata to false on the source component?


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Marked as answer by Samuel Vanga Monday, April 09, 2012 9:53 PM
    Monday, April 09, 2012 8:22 PM