August 10, 2020 at 7:13 pm
When using the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) in the Import Wizard, I've noticed it takes much longer to import data compared to using the Native Client. Are there any properties you can change to help speed this up?
August 11, 2020 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 12, 2020 at 1:16 am
I'm not aware of any settings related to the nature of the OLEDB connection, but you can always search Google and see what you can or can't find, ... and alternately, just start browsing through all the options within SSMS and see what you see. However, the Native Client is ALWAYS likely to be more efficient with data import, so unless you have no alternative, I'd not waste the time with OLEDB.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 12, 2020 at 11:12 am
My previous Google searches told me that the native client is deprecated and not recommended for use in new developments. I know that means it could function for many years to come but I'm trying to stay ahead of the curve if possible on this one. The other issue with native client is that it times out when referencing an AG Listener with multiple IP subnets.
August 20, 2020 at 6:22 pm
I wanted to share a discovery I made regarding this subject. I used the import wizard to import a flat file into SQL using the Native Client and saved it as an SSIS package. This completed within ten seconds. Conversely I used Microsoft OLE DB Driver for SQL Server to import the same file and that took over twenty minutes. I can then edit the SSIS package created by the first import to use Microsoft OLE DB Driver for SQL Server and it will only take ten seconds. What would cause a difference between importing using the Native Client and then later editing that versus importing only as Microsoft OLE DB Driver for SQL Server?
August 21, 2020 at 2:55 pm
SSIS and SSMS are in two different categories where connectivity is concerned. SSMS has that import ability at only the most basic level, and can't be effectively optimized, whereas SSIS is completely configurable, and is designed to work correctly for data import and export "out of the box", so to speak. Trying to compare the two is like asking a single banana to be compared to a multi-tool, in an SHTF survival scenario. The former will feed you for maybe an hour or two, but the latter makes it possible to perhaps create fire, or create new tools to survive far longer.
Also, SQL Native Client was originally developed specifically to work with moving data to and from SQL Server and SSMS. SSIS gives you so many more options. That's why SSMS works so well with Native Client and not so well with OLE DB.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2020 at 1:22 pm
That part makes sense but what is still eluding me is that if I create the SSIS package from scratching using the OLE DB Driver then it still takes 20+ minutes. It only runs within a few seconds after I've already created the package using the import wizard and native connection, then editing the connection in SSIS. It's like there is some other property being set but the connection strings are identical when I compare the two.
August 25, 2020 at 1:35 pm
There's quite likely a difference between the two packages... I'd examine both dtsx packages with a file comparison tool and see what is different. There are ways to tune a data flow that can increase the buffer size and number of engine threads. Both of those settings can drastically improve or destroy performance.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 27, 2020 at 5:44 pm
The two differences that I can see are
There are no glaring differences to me so the mystery remains how to improve the import speed using the MSOLEDBSQL driver via the import wizward.
December 16, 2020 at 3:08 pm
We had a similar situation at my shop where existing packages within a project seemed not to be taking advantage of the speed of the MSOLEDBSQL driver. What I found was that unless the .dtproj file is updated then the packages within that project will continue using the same connection the project was originally created with. I modified the connection strings and driver name in Visual Studio by clicking on the 'View Code' option, saved it, and then recompiled the project. I had a few projects where even the recompile did not update the value in the .dtproj file. I simply opened the file in my repo and changed the value manually and saved it. Git recognized this when I went back into Visual Studio, so I accepted the update, recompiled, and then release the Project to the server. The packages then began using the new driver. You can check to make sure what driver is being used by checking the reports on the Server in the Integration Services Catalog. In my case, I saw the old driver was being used and that's what clued me in and led me to the .dtproj file. By the way, the .1 after the MSOLEDBSQL just means that someone installed a second copy of the driver onto the server. Somehow Visual Studio recognizes this and uses the second copy with the .1 at the end.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply