I have been importing an excel file with approximately 250,000 rows of data and 40 columns to SSMS on a weekly basis through the Import Wizard. Unfortunately, the import times take around 45 minutes, which is a bit longer than I would like.

The main issue I am facing is that I am not able to access SSIS or use BulkInsert queries due to a client restriction. I am left with trying to import using the Import Wizard exclusively.

Are there any tricks I could try to maybe make the import run faster? The server I am on itself doesn't have much RAM so could that also be a limiting factor?

I have tried saving as a CSV and importing, but get a flow task error informing me that the import data did not match the target destination type. This is because I have a number of columns specified as datetime or int. Is there a way around this without having to edit each column in the advanced area for selecting my flat file source?

  • 3
    Have you tried exporting the excel file to a .csv and importing that instead? I find that larger excel files are much more cumbersome to interact with, and converting your input file to a .csv may improve overall performance as it won't need to go through the Jet driver. – John Eisbrener Oct 29 '19 at 14:34
  • 1
    You probably want to merge your two accounts – mustaccio Oct 29 '19 at 16:01
  • Is it possible for you to use BCP? A CSV export and a BCP import should do the trick as well. Alternately, you import the CSV into a staging table, and manipulate the data inside SQL Server before sending it to its final destination. – Laughing Vergil Oct 29 '19 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.