title: Importing Large Excel Sheet into SQL
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>Any thoughts on why the import only works on small spreadsheets unless the spreadsheet it also opened in Excel?</p>
  
  <p>I'm using SQL Server 2008 R2 on Windows 10 Pro and Office 2016. I also have the AccessDatabaseEngine - 2007 installed.</p>
  
  <p><strong>EDIT:</strong>
  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.</p>