July 20, 2017 at 7:26 am
Had a problem today with OLE DB source from a view on 2008 R2. With Data Access Mode set to Table or View, selected my view from my source DB which I want to load in to a table on the same database. The view is "interesting" in that it gathers data from 3 different databases on the source SQL server, but the view itself is in the database the connection is associated with. Said view produces all the data - approx. 200k rows, in about 30 seconds in SSMS. The destination is an equivalent table of the view, field for field. The view appears successfully when previewing in the OLE DB Source form in VS.
In VS2008 Bids, the OLE DB source just sits there, all nice and yellow, doing nothing. Looking at progress it has passed all its validation, and is just sitting at the beginning of the execute phase. I killed it after 5 minutes but firmly believe that it'd sit there all day in that state. Multiple rebuilds, deleting and recreating the process in SSIS, outputting to a flat file instead, etc. had no effect. Looking at what queries are executing on the server when the package is running, I see my view is being executed in "SELECT * FROM dbo.v_..." form. To reiterate - according to BIDS, nothing is coming out of the source.
Performing the equivalent operation in T-SQL/SSMS directly (INSERT INTO ... SELECT * FROM dbo.v_...), is a 30 second load.
After a bit of Googling, didn't find anything definitive but did see some things around 4 part naming on queries and selecting the correct data access mode. As a punt I changed the OLE DB source's data access mode to SQL Command and did the SELECT * FROM dbo.v_...; directly, and it worked. (My view does not have 4 part naming.)
So, the question is, given that essentially the same command has been sent to the server, why does the SQL command work, and the Table or View not? I don't like not having a proper error or explanation to this issue!
Thanks all.
Simon
July 22, 2017 at 8:18 am
I can't say for sure, but I suspect that something is happening when SSIS queries the server for metadata. Using a SQL Command you have provided the column names that you want. Someone with more detailed knowledge of the workings of SSIS will need to confirm or deny this.
Russel Loski, MCSE Business Intelligence, Data Platform
July 24, 2017 at 7:20 am
I can neither confirm nor deny what Russel said 🙂
It's a long time known issue:
https://blogs.msdn.microsoft.com/sqlperf/2007/04/29/set-up-ole-db-source-to-read-from-view-efficiently/
I've always used the "direct query' or from "variable" option, I then pick only the columns I need from my source. SSIS performance is very much tied to the amount of data going through. I think it's considered a best practice.
It looks like with SSDT 17, you don't even have the option to pick from a table or view using an OLEDB source connection anymore.
July 24, 2017 at 7:34 am
Thanks Tom - just what I was after but failed to find in my own attempts to investigate this issue. I take note of your comment that the Table or View option isn't even an option any more.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply