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 2022, 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 2022. This lesson walks through both tools in practical terms.

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.

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

There are four standard ways to open the wizard:

Method 1: SQL Server Management Studio (SSMS)

1. Connect to your SQL Server 2022 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.

Method 2: Windows Start Menu

1. Open the Start Menu and search for SQL Server Import and Export Data.
2. Choose the 64-bit version on modern hardware, or the 32-bit version when connecting to a 32-bit data source such as an older Access database driver.

Method 3: Command Prompt

Run DTSWizard.exe directly. The default installation paths are:

64-bit: C:\Program Files\Microsoft SQL Server\160\DTS\Binn\DTSWizard.exe
32-bit: C:\Program Files (x86)\Microsoft SQL Server\160\DTS\Binn\DTSWizard.exe

The folder number 160 corresponds to SQL Server 2022. Earlier versions use 150 (SQL Server 2019), 140 (2017), and so on.

Method 4: Azure Data Studio

Azure Data Studio does not ship with the Import and Export Wizard built in, but you can install the SQL Server Import extension from the Extensions Marketplace. This extension provides a flat-file import workflow directly inside Azure Data Studio and is a practical alternative when SSMS is not installed on the workstation.

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 (SQL Server Native Client, 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.
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.
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 2022.

When data integration requirements grow beyond what the wizard handles, SSIS packages developed in Visual Studio 2022 with the SQL Server Data Tools (SSDT) extension are the standard solution. An SSIS package is a saved unit of work stored as a .dtsx file. It contains a control flow (the sequence of tasks), one or more data flows (the movement and transformation of rows), connection managers, event handlers, and variables. The following steps cover the full setup and deployment sequence for SQL Server 2022.

Step 1: Install Visual Studio 2022 with SSDT

1. Download Visual Studio 2022 from visualstudio.microsoft.com if it is not already installed.
2. Open the Visual Studio Installer and click Modify on your Visual Studio 2022 installation.
3. On the Workloads tab, under Data storage and processing, check SQL Server Data Tools.
4. Click Modify to apply. Visual Studio will download and install the SSDT components.

Note: Analysis Services, Integration Services, and Reporting Services project templates are delivered as separate Visual Studio Marketplace extensions, not as part of the base SSDT workload.

Step 2: Install the SSIS Extension

1. Launch Visual Studio 2022.
2. Go to Extensions > Manage Extensions.
3. Search the Online tab for SQL Server Integration Services Projects 2022.
4. Click Download and restart Visual Studio when prompted.

This extension adds the Integration Services project template and the SSIS Designer to Visual Studio. Without it, .dtsx files open as raw XML.

Step 3: Create a New SSIS Project

1. Go to File > New > Project.
2. Search for Integration Services Project and select it.
3. Provide a project name and location, then click Create.

Visual Studio creates the project with a default package named Package.dtsx already open in the SSIS Designer.

Step 4: Set the Target SQL Server Version

1. In Solution Explorer, right-click the project and select Properties.
2. Under Configuration Properties > General, set TargetServerVersion to SQL Server 2022.

Setting the target version before designing the package prevents deployment errors caused by components or features that are not available in an older catalog version.

Step 5: Design the Control Flow and Data Flow

An SSIS package has two design surfaces. The Control Flow tab defines the sequence of tasks and the precedence constraints between them. Common control flow tasks include the Data Flow Task, the Execute SQL Task (for running T-SQL statements), the File System Task (for moving or archiving files), and the Script Task (for C# or VB.NET logic that has no built-in task equivalent).

The Data Flow tab, accessed by double-clicking a Data Flow Task, defines how rows move from source to destination. A typical data flow contains:

1. A source component such as OLE DB Source, Flat File Source, or Excel Source. Configure the connection manager and choose the table or SQL query that produces the input rows.
2. Transformation components as needed. Common transformations include Lookup (joining a reference table to enrich rows), Derived Column (computing new values with SSIS expressions), Conditional Split (routing rows to different outputs based on a condition, analogous to a WHERE clause with multiple branches), and Aggregate (performing GROUP BY operations in the data flow).
3. A destination component such as OLE DB Destination or Flat File Destination. Map the incoming columns to the destination columns in the Column Mappings editor.

Step 6: Deploy and Execute the Package

1. Build the solution. Go to Build > Build Solution. Resolve any validation errors before continuing.
2. Deploy the project. Right-click the project in Solution Explorer and select Deploy. The Integration Services Deployment Wizard guides you through connecting to the target SQL Server instance and selecting the SSISDB catalog folder.
3. Execute the package. In SSMS, expand Integration Services Catalogs > SSISDB, navigate to your project folder, right-click the package, and select Execute. To schedule recurring execution, create a SQL Server Agent job with an Integration Services Package step pointing to the deployed package.

Note: The SQL Server Agent service must be running to schedule package execution. Verify its status in SSMS under the SQL Server Agent node before creating the job.

Using WITH, JSON, and VECTOR in SSIS Data Flows

SQL Server 2022 added native JSON functions, the REGEXP family of pattern-matching functions, and the VECTOR and VECTOR_DISTANCE functions for AI similarity search. These can be used inside SSIS through the Execute SQL Task and the OLE DB Source component. For example, an OLE DB Source can issue a query using a Common Table Expression (WITH clause) to pre-aggregate data before it enters the data flow, reducing the volume of rows that transformation components must process. A VECTOR_DISTANCE call in a source query can pre-filter rows by semantic similarity before they 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. These SQL Server 2022 features do not require any change to the SSIS package model itself - they are expressed as T-SQL inside the source query or Execute SQL Task, and SSIS transports the result set as it would any other rowset.

[1] SQL Server Integration Services (SSIS): SSIS in SQL Server 2022 is a robust platform for building high-performance data integration and transformation solutions. It provides a comprehensive set of tasks and components for ETL processes, enabling the movement and manipulation of data from a wide range of sources to destination systems. SSIS remains a key component for data warehousing and automation of data-related workflows, offering strong performance and broad integration capabilities within the SQL Server ecosystem.