VS 2012 - SSIS - Importing Excel Files - Lockups RRS feed

  • Question

  • I have a quarterly application where I need to import several sizable .xlsx files into a SQL database (then manipulate the data).  I am using VS2012 on the same server (64bit Win2008 R2 on a VM with 4gig of ram) as the database and creating SSIS routines to import, manipulate and export the data.   I have created a couple of simple modules that contain three components:

    Excel data source -> Data Transform -> SQL Server destination

    (The data transform simply converts date to dbdate and float to money)

    The bigger the Excel file, the more difficult it is to work with when editing/using that specific portion of the project.  Some of the files are 200,000 rows by 30 columns.  (Large but not ridiculously so.)  It acts as though every time the Excel data source is touched, it has to read the entire file and validate every cell.  To obtain some sort of programming productivity, I have to split the Excel files into chunks of no more than 50,000 rows and deal with them as multiple imports.

    More often than not, if I try to deal with more than 100,000 rows, VS will choke and gag and eventually stop responding.  I can't tell you how many time I have had to force shut down VS.  Even when it is not locked up, it will go into "Not Responding" and "Waiting for Internal Process" mode for 3-5 minutes.  My productivity is in the tank whenever I touch this project.  I have tried setting "DelayValidation" to true but it doesn't seem to make a difference.

    Any suggestions?
    • Edited by DLatch Friday, October 4, 2013 4:04 PM
    Friday, October 4, 2013 3:54 PM


All replies