SQL mdf and log files

SSIS and Import/Export
2
  1. Put the database in SIMPLE recovery mode.
  2. batch the inserts (say, 100,000 rows per commit) to keep log requirements low. Alternatively, do a BULK INSERT if possible with an ORDER clause so SQL can use minimal logging
  3. SQL 2012 or up? Consider building a COLUMNSTORE index. You'll get space savings and performance improvements.
  4. Why are you worried about the size of the mdf file?
  5. Consider partitioning your destination table and using partition switching to move data in/out. That's a metadata operation with no actual data movement.
  6. Index fragmentation is not directly related to mdf growth per se. however, a good practice is to disable/drop indexes before a large load then re-create/rebuild the indexes when finished.