April 13, 2011 at 10:35 am
Hello,
I am experiencing an issue in SSIS which neither I nor the senior DBA are having any luck getting to the bottom of. Here's the scenario:
We have a view in which one column is converted from varchar to decimal(20,0). We can do a SELECT * FROM the view with no problem, ~8900 rows are returned with no errors. There is a very simple SSIS package with a single data flow task to take the contents of this view and insert them into a table in another SQL Server database.
When the OLE DB Data Source object is configured to use "Table or View" with the aforementioned view as the source, it fails validation with the error DTS_E_OLEDBERROR 0x80040E07 "error converting data type varchar to numeric". However, if the OLE DB Data Source is changed to "SQL command" instead with SELECT * FROM [view name], then the validation error goes away.
Has anyone else encountered something similar? We're stumped as to why SSIS would throw this conversion error when a SELECT * FROM the view in question works perfectly.
April 16, 2011 at 3:44 pm
Got this one figured out. Short version for the curious: "Table or View" access mode in the OLE DB Data Source = crappy execution plan.
For a bit more background, the view in question exists on one database, but actually queries tables in a separate database, 6 or so tables joined in the query if memory serves (I'm at home and can't look at it currently).
We ran a Profiler trace with Showplan All included, and compared the execution plans from a SELECT * FROM [view] in SSMS versus executing the Data Flow Task in the SSIS package. The SELECT in SSMS used a very simple query plan. However, we noticed the SSIS package called SET ROWCOUNT 1 followed by a much nastier plan being used.
For kicks, we ran the SELECT again in SSMS but added SET ROWCOUNT 1 ahead of it, and watched the trace. Voila, same big nasty plan used when running the SSIS data flow task. And, it threw the same conversion error in SSMS which we had seen in SSIS.
We changed the OLE DB Data Source object to use "SQL Command" access mode, with the query SELECT * FROM [view], and this solved the problem.
Did some research and learned that the OLE DB Data Source uses SET ROWCOUNT 1 in "Table or View" access mode in order to get the table metadata during the validation phase. This causes a different, in this case less efficient execution plan to be used, and also in our case something in the chosen plan caused a type conversion error, even though the explicit conversion being done in the view works perfectly fine with the data at hand. The only way to avoid this appears to be to use "SQL Command" mode instead.
April 18, 2011 at 12:10 am
Good that you find the solution and thanks for posting it here.
I'd just want to add that one should always use the SQL command instead of the "table or view" option, because, yeah, it just sucks
(it is even worse than select *)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy