View SQL Code Behind Flat File Import Wizard?

Question

Is there a way to view and copy the code that the "Import Flat File to SQL" Wizard ultimately uses, after I have gone through all the steps (chosen file, set up data types for my columns, etc.)?

While using the Import Flat File to SQL wizard, I found it cumbersome to have to select a data type for each column individually. My data had a large amount of columns to change. In the future, I would prefer doing this via SQL code to save time. I’m learning Microsoft SQL Server Studio and currently only know how to use the Import Flat File Wizard, so I would need to learn all the steps for doing it another way.

Version: 18.7.1

asked 2021-05-10 by NatY

Answer

The "Import Flat File to SQL Wizard" is actually just an abbreviated version of the more complex "Import & Export Wizard." It uses the exact same mechanism, but streamlines the process by skipping unnecessary options & picking appropriate defaults for you. Ultimately, the import process uses the same mechanism though.

One of the "hidden" options that is only available in the Import & Export Wizard, is to generate an SSIS Package, rather than executing immediately. The import actually happens via SSIS as a mechanism, rather than pure T-SQL or PowerShell code. Because it is not executing T-SQL or PowerShell directly, it can only output the thing it generates & runs behind the scenes–an SSIS Package.

To access the Import & Export Wizard, right-click on a database, and select `Tasks–>Import Data…" (this is right below "Import Flat File…" on the context menu). Here, you can choose a flat file source, and SQL Server destination.

On the last page of the Import & Export Wizard, you can select to Save as an SSIS Package, or run immediately. If you save as an SSIS package, you will be able to see exactly what SQL Server is doing.