SQL Server Import and Export Wizard

SQL data integration is the process of moving, consolidating, and transforming data between systems so that decision-makers and applications always work from a single, consistent source of truth. In SQL Server 2025, Microsoft provides a layered toolkit for this work. At the entry level sits the SQL Server Import and Export Wizard, a point-and-click utility built on top of SQL Server Integration Services (SSIS) that handles straightforward data transfers without requiring any code. For production-grade ETL pipelines, SSIS itself provides a full visual development environment inside Visual Studio, covered in depth two lessons ago. This lesson focuses on the wizard itself: what it is, how it's actually launched on a current installation, and what it's still not built to do.

Background: From DTS to SSIS

Before SQL Server 2005, Microsoft shipped a data-movement tool called Data Transformation Services (DTS). DTS was useful for its era but was limited in scalability, error handling, and maintainability. SQL Server 2005 replaced it entirely with SQL Server Integration Services (SSIS), which introduced a proper package model, a visual data-flow designer, and robust logging. DTS is not available in any currently supported version of SQL Server. If you encounter documentation that references DTSWizard.exe or DTS packages, treat it as a historical artifact. The executable name DTSWizard.exe was reused for the modern Import and Export Wizard as a legacy shortcut, but the tool itself runs entirely on the SSIS engine, the same rename this course has already covered elsewhere in this module.

What the Import and Export Wizard Does

The Import and Export Wizard is the fastest path for one-time or occasional data transfers. It supports a wide range of sources and destinations including SQL Server databases, flat files (CSV, TXT), Microsoft Excel, Microsoft Access, Oracle, and any OLE DB or ODBC-compliant data source. The wizard generates an SSIS package behind the scenes and either runs it immediately or saves it for later use. It does not require Visual Studio or any development environment.

Typical use cases include loading a vendor-supplied CSV file into a staging table, exporting a query result to Excel for a business analyst, migrating a table from one SQL Server instance to another, and doing a quick data validation pull between two databases.

Launching the Import and Export Wizard

A strictly 64-bit architecture and a modular SSMS installer change a few of the practical details for launching the wizard on SQL Server 2025, compared with earlier versions. There are still several standard ways to open it:

Method 1: SQL Server Management Studio (SSMS)

1. Connect to your SQL Server 2025 instance in Object Explorer.
2. Expand the Databases node and right-click the target database.
3. Select Tasks, then choose either Import Data or Export Data.
4. The wizard opens with the selected database pre-populated as the source or destination.

One caveat is worth knowing before relying on this path: Microsoft's own announcements show SSIS support returning to SSMS 21 as a preview feature starting with the 21.2.5 point release, not from SSMS 21's initial general availability. On an installation still running an earlier SSMS 21 build, Tasks > Import Data and Tasks > Export Data may be missing or greyed out. The fix is to add the SSIS-related component through the Visual Studio Installer's Modify option, the same mechanism Visual Studio itself uses to add the SQL Server Data Tools workload this course already covered. Where that menu path isn't available at all, either of the next two methods still works regardless of what SSMS itself currently supports.

Method 2: Windows Start Menu

1. Open the Start Menu, expand the Microsoft SQL Server 2025 folder, and select SQL Server 2025 Import and Export Data (64-bit).

Earlier SQL Server versions offered a 32-bit shortcut alongside the 64-bit one, useful when connecting to an older 32-bit-only driver. SQL Server 2025 and SSMS 21 no longer ship a 32-bit DTSWizard.exe at all, consistent with the broader 32-bit deprecation this course has already covered for SSIS. If a transfer reads from or writes to Excel, Access, or a third-party ODBC or OLE DB source, the matching 64-bit driver, the 64-bit Microsoft Access Database Engine Redistributable covers Excel and Access specifically, has to be installed on the machine, or that data source simply won't appear in the wizard's dropdown list.

Method 3: Command Prompt

Run DTSWizard.exe directly. SQL Server 2025 is version 17.0, so its engine folder is numbered 170, following the same pattern as earlier releases:

SQL Server 2025 engine path:
C:\Program Files\Microsoft SQL Server\170\DTS\Binn\DTSWizard.exe

SSMS 21 install path:
C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\CommonExtensions\Microsoft\SSIS\170\Binn\DTSWizard.exe

The folder number 170 corresponds to SQL Server 2025, following the same numbering this course already relied on for version-specific paths elsewhere in this module. Earlier versions used 160 (SQL Server 2022), 150 (2019), and 140 (2017). Both paths above launch the same underlying wizard; which one exists on a given machine depends on whether the engine, SSMS, or both are installed there.

Method 4: Import Flat File (No Drivers Required)

For a simple CSV or text file import into a new table, SSMS's Tasks > Import Flat File is a separate, lighter wizard built for exactly that narrower case. It skips the OLE DB and ODBC provider selection entirely, so there's no need to configure a 64-bit driver first, at the cost of only working with simple flat files rather than the full range of sources the main wizard supports.

If the Wizard Won't Open at All

SQL Server 2025's initial release shipped with a confirmed bug (tracked as bug 5003709): DTSWizard.exe wouldn't open if the Database Engine was installed without also checking the SQL Server Integration Services feature box during setup. Microsoft's Cumulative Update 4 release notes (KB5081495, released April 2026) confirm the fix. A wizard that refuses to launch at all on a fresh SQL Server 2025 install is worth checking against both of those, whether SSIS was actually selected during setup, and whether CU4 or later is installed, before assuming something else is broken.

Walking Through the Wizard

Regardless of how you launch it, the wizard presents the same sequence of screens:

1. Choose a Data Source. Select the provider type (Microsoft OLE DB Provider for SQL Server, Flat File Source, Microsoft Excel, and so on) and supply the connection details. For SQL Server sources, provide the server name, authentication method, and database name. The legacy SQL Server Native Client no longer appears here at all; it's been fully superseded by MSOLEDBSQL and the .NET Framework Data Provider for SqlServer.
2. Choose a Destination. Same provider list, now for the target. You can mix providers freely, for example, SQL Server as source and a CSV flat file as destination.
3. Specify Table Copy or Query. Choose between copying one or more entire tables, or writing a SELECT statement to filter and shape the data before it moves.
4. Select Source Tables and Views. Map source objects to destination tables. The wizard can create the destination table automatically if it does not exist, inferring column names and data types from the source.
5. Review Data Type Mapping. Inspect any type conversions the wizard has inferred. Common mismatches include VARCHAR length differences and NULL versus NOT NULL constraints. Correct these before proceeding.
6. Run Immediately or Save as SSIS Package. For one-time jobs, run immediately. If the transfer will repeat on a schedule, save the generated SSIS package to SQL Server or the file system and schedule it via SQL Server Agent, the exact pairing this course built out in full two lessons ago.
7. Complete the Wizard. Review the summary screen and click Finish. The wizard displays a progress screen with row counts and any errors or warnings per table.

Limitations of the Import and Export Wizard

The wizard covers straightforward transfers well but has real limits. It does not support conditional logic, looping, error redirection to a quarantine table, or incremental loads based on a watermark column. For any of those requirements, you need a full SSIS package developed in Visual Studio with SQL Server Data Tools, which this course has already covered building, deploying, and scheduling in detail.

When data integration requirements grow beyond what the wizard handles, conditional logic, error redirection, a watermark-based incremental load, an SSIS package developed with SQL Server Data Tools is the standard solution, exactly the full six-step process this course already walked through two lessons ago: installing Visual Studio with the SSIS project extension, creating an Integration Services project, setting the target server version, designing the control flow and data flow, and deploying and executing the finished package against the SSISDB catalog. None of that needs repeating here; what's worth adding in this lesson instead is one thing that lesson didn't cover: using this course's own newer T-SQL features directly inside an SSIS data flow's source query.

Using WITH, JSON, and VECTOR in SSIS Data Flows

SQL Server's native JSON functions, the REGEXP family of pattern-matching functions, and the VECTOR and VECTOR_DISTANCE functions for AI similarity search, all covered earlier in this course, aren't limited to queries run directly against the database. They work the same way inside SSIS, through the Execute SQL Task and the OLE DB Source component. An OLE DB Source can issue a query using a Common Table Expression (a WITH clause) to pre-aggregate data before it enters the data flow, reducing the volume of rows that transformation components have to process downstream. A VECTOR_DISTANCE call in a source query can pre-filter rows by semantic similarity before they ever reach a Conditional Split. JSON_VALUE and JSON_QUERY extract scalar values and sub-objects from JSON columns, making it straightforward to flatten semi-structured data into relational rows inside a Derived Column transformation, rather than handling that flattening with custom script code. None of these features require any change to the SSIS package model itself; they're expressed as ordinary T-SQL inside the source query or an Execute SQL Task, and SSIS transports the result set exactly as it would any other rowset.

Suppose a new reporting workstation needs a one-time copy of this course's own Inventory table, StockNum, Cost, RetailPrice, QtyAvailable, and LastPriceChangeDate, loaded into a local SQL Server 2025 instance for an analyst to work against without touching production. Nothing about that job needs the conditional logic or error redirection a full SSIS package is built for, which makes it exactly the case the wizard exists for:

1. Right-click the destination database in Object Explorer, select Tasks > Import Data, and choose SQL Server Native Client's modern replacement, Microsoft OLE DB Provider for SQL Server, as the source provider.
2. Point the source connection at the production instance and database holding Inventory, and the destination connection at the reporting workstation's own instance.
3. On the Specify Table Copy or Query screen, choose Copy Data from One or More Tables, rather than writing a query, since the whole table is wanted as-is.
4. On the table mapping screen, select Inventory and let the wizard create a matching destination table automatically, since one doesn't already exist on the reporting workstation.
5. Review the data type mapping screen; a column like RetailPrice mapping cleanly to the same numeric type on both sides is the expected, quiet outcome, and the one worth double-checking for before clicking through past it.
6. Choose Run Immediately, since this is a genuine one-time copy, and finish the wizard.

If this same copy needed to happen every morning instead of once, the only change would be on that last screen: saving the generated SSIS package rather than running it immediately, then scheduling that saved package with SQL Server Agent exactly the way this course already covered doing for ETL_SalesData_Import. The wizard doesn't need to be run by hand twice to get there; it only needs to be told, the first time through, that the result is worth keeping.

Each connection screen in the wizard asks for an authentication method the same way any other SQL Server connection does: Windows Authentication, SQL Server Authentication, or, on a current installation, Microsoft Entra ID, the same identity option this course has already covered for SSIS's own connection managers and for Azure Data Factory's connections back to SQL Server. Running the wizard interactively under a personal Windows or Entra ID account is the common case for a one-time job like the one above, and it's worth remembering that whatever account runs the wizard needs read access to every source object being copied and write access, including CREATE TABLE permission when the destination table doesn't already exist, on the destination side. A wizard run that fails partway through with a permissions error on the destination is far more often a missing CREATE TABLE grant than a problem with the transfer itself.

Once a transfer is saved as an SSIS package and scheduled rather than run by hand, the same least-privilege principle this course applied to SQL Server Agent's proxy accounts applies here too: the account the scheduled job actually runs under should hold only the permissions that specific package's connections need, not whatever broader access happened to be available to whoever first ran the wizard interactively.

This lesson and the SSDT lesson it points back to cover the two ends of the same spectrum this module keeps returning to: a one-time or occasional transfer, with no transformation logic beyond what the wizard's own screens expose, is squarely the wizard's job, run once and discarded, or saved as a package and run again later without ever opening Visual Studio. A transfer that needs real conditional logic, error handling, or a watermark-driven incremental load earns the full SSIS package this course already built out in SSDT, scheduled with SQL Server Agent, and, where the workload has moved to Azure, lifted into Azure Data Factory through the two paths the previous lesson covered. The wizard isn't a lesser version of that package; it's the right answer for a narrower, more common job, and knowing which one a given transfer actually needs is as much a part of this skill as knowing how to run either one.