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

Answer 1

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

Answer 2

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

Answer 3

Do you happen to have two sources in your Data Flow?

---

Talk to me now on

Answer 4

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

Answer 5

Do you happen to have two sources in your Data Flow?

---

Talk to me now on

Todd - No, i have only one data source.

---

Regards, Samuel Vanga Twitter Blog

Answer 6

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

Answer 7

Does the behavior change if you give your query a field list instead of Select * ?

---

Chuck

Answer 8

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