Monthly I'm given spreadsheets which I need to import into SQL. What I normally do is use the SQL Server Import and Export wizard and import into a temp table where I then call stored procedures to complete the import process. This all is working fine on my production server and was working fine on my development machine until last week when I did a clean install of Windows 10. Now, I'm still able to import smaller spreadsheets with only a few thousand rows, however, if I attempt to import data from spreadsheets which have 500,000+ rows of data it takes a couple minutes on both "Setting Source Connection" and "Setting Destination Connection" then seems to hang on "Copying". I've checked logs and run SQL Profiler and see nothing regarding failures. I've let the process run for upward of an hour and eventually killed it out, viewed the temp table and shows no records were imported. The data format is good as I'm able to import on my production server without issue.
Now here's the kicker... I'm able to import these large sheets on my development server only if I have the spreadsheet open in Excel at the same time, (note that for smaller spreadsheets I don't need to have the spreadsheet open to import). With the spreadsheet open, the import process works but is very slow and not an effective way of doing things.
Any thoughts on why the import only works on small spreadsheets unless the spreadsheet it also opened in Excel?
I'm using SQL Server 2008 R2 on Windows 10 Pro and Office 2016. I also have the AccessDatabaseEngine - 2007 installed.
EDIT: I created an SSIS package and ran it as suggested and everything worked as expected. To make things even more strange, I went through the Import/Export Wizard and chose to Run Immediately and create SSIS and everything again worked as expected. I went through the wizard again but chose just to Run Immediately (did not create SSIS) and it hangs up copying still. I attempted to run it 4 more times and each time it works if choosing "Run Immediately" and "Create SSIS" but each time fails when choosing only "Run Immediately" (without creating an SSIS). I'm honestly confused how this can happen and am open to more suggestions.
Finally found a solution to this problem!
Since I was able to import the sheets when open, that made me think my issues was due to a slow connection between SQL and Excel, so I started looking in that direction. I found a few posts on other sites talking about slow ODBC connections with Windows 8 & 10. One site mentioned switching the driver to "SQL Native Client". I was choosing my destination as "SQL Server Native Client 10.0", I switched to "SQL Native Client" as suggested and everything now is working as expected.
I see the these two drivers listed as:
Driver Version
------------------------------------------------
SQL Native Client 2005.90.3042.00
SQL Server Native Client 10.0 2009.100.6000.34
I'm guessing the older (2005) SQL Native Client works is because the Import/Export utilizes the AccessDatabaseEngine - 2007.
NOTE: When using Windows 7 or 2008 Server, I had no issues importing large spreadsheets with the (2009) SQL Server Native Client 10.0. This seems to only be an issue with later versions of Windows. I see several other sites mentioned there were performance issues using TCP for their ODBC connections on Windows 10 and they saw a noticeable improvement using Named Pipes instead. As my production server uses TCP, I didn't make the change to Named Pipes on my development machine so I can't confirm that, but wanted to mention it in the event some comes across this post having that issue.