Enterprise Business Rules · Module 7

Import Data using SQL Server Import and Export Wizard (SSIS)

Lesson 2

Import Data with the SQL Server Import and Export Wizard

Objective: Configure and execute a guided data import into SQL Server 2025 using the 64-bit SQL Server Import and Export Wizard, while managing provider architecture, column mappings, staging tables, and error handling.

The SQL Server Import and Export Wizard (DTSWizard.exe) provides an interactive graphical interface for copying data between supported data sources and destinations. Underneath the GUI, the wizard dynamically constructs a SQL Server Integration Services (SSIS) data flow pipeline. In this lesson, the target environment is SQL Server 2025 (17.x). While the wizard excels at ad-hoc imports, prototyping, and straightforward table copies, complex production pipelines requiring conditional branching, data cleansing, or audit frameworks should be built and maintained as formal SSIS projects in Visual Studio.

Preparing the Environment and Provider Architecture

Before launching the wizard, create a dedicated staging table in a non-production database with explicitly defined column data types, lengths, and nullability constraints. Verify network connectivity, firewall rules, and file-system permissions for both the source and destination environments.

Because the wizard relies on external data providers (OLE DB, ODBC, or ADO.NET) to connect to sources and targets, matching the processor architecture (bitness) of the driver to the wizard executable is critical:

  • 64-Bit Architecture in SQL Server 2025 and SSMS 21: Modern SQL Server 2025 client tools, SQL Server Management Studio (SSMS 21), and SQL Server Integration Services Projects 2022 run natively as 64-bit applications. Disregard legacy documentation claiming that SSMS only launches the 32-bit wizard.
  • Executable Location: In SQL Server 2025 (version 170), the 64-bit wizard resides at C:\Program Files\Microsoft SQL Server\170\DTS\Binn\DTSWizard.exe.
  • Destination Driver Selection: Avoid deprecated providers such as the legacy "Microsoft OLE DB Provider for SQL Server" (SQLOLEDB) or "SQL Server Native Client" (SQLNCLI11). Always select the current Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL or MSOLEDBSQL19) or the .NET Framework Data Provider for SqlServer, which support TLS 1.3, TDS 8.0, and Microsoft Entra authentication.
  • Flat File and Excel Sources: If importing from Microsoft Excel (.xlsx) or Access, ensure the 64-bit Microsoft Access Database Engine Redistributable (ACE OLE DB provider) is installed to match the 64-bit wizard.

Step-by-Step Walkthrough: Executing a Guided Import

  1. Launch the Wizard: In SSMS 21 Object Explorer, connect to your SQL Server 2025 instance, expand Databases, right-click the target database, and select Tasks > Import Data... (Alternatively, launch DTSWizard.exe directly from the Windows Start menu or command prompt.)
  2. Choose a Data Source: Select the appropriate data source provider. When selecting Flat File Source, browse to the file and verify the Locale, Code page (for example, 65001 (UTF-8) vs. 1252 (ANSI - Latin I)), Format (Delimited, Fixed width, or Ragged right), Text qualifier (commonly double quotes " for CSVs containing embedded commas), and confirm whether the Column names in the first data row checkbox applies.
  3. Choose a Destination: Select Microsoft OLE DB Driver for SQL Server. Enter the target server and instance name, select your authentication method (Windows Authentication, SQL Server Authentication, or Microsoft Entra ID), configure encryption settings, and select the target database.
  4. Specify Table Copy or Query: Choose whether to copy entire tables/views or write a specific SQL query to filter rows at the source. (Note: This screen is bypassed when importing from a Flat File Source.)
  5. Configure Column Mappings and Transformations: Select the target staging table and click Edit Mappings.... Inspect every source-to-destination column pairing:
    • Choose whether to Append rows to the destination table or Delete rows in destination table prior to loading.
    • Enable Enable identity insert only if the source file supplies explicit keys for an IDENTITY column (which translates to SET IDENTITY_INSERT ON during the load).
    • Verify data types, character lengths, numeric precision/scale, and nullability.
  6. Configure Error and Truncation Handling (Flat Files): Review the flat file advanced column properties or error output rules. Decide whether data conversion errors or string truncations should fail the entire batch immediately or redirect offending rows for investigation.
  7. Preview and Execute: Click Preview... to inspect the first 100-200 parsed rows. Look specifically for misaligned columns (caused by unescaped delimiters), garbled diacritics or special characters (encoding mismatch), and truncated strings. Proceed to the Save and Run Package screen, select Run immediately, review the summary, and click Finish. Audit the execution report to confirm the transferred row count matches the source.

Do Not Let Wizard Defaults Define Your Data Model

When you allow the Import and Export Wizard to create a new destination table automatically, or when you connect a Flat File Source, the wizard samples only the first 50 to 200 rows to guess column data types and lengths. Relying on this automatic inference in production introduces three common defects:

  • Corrupted Identifiers (Leading Zero Loss): Postal codes (such as 02115), part numbers, or account codes that contain only digits in the initial sample rows are often inferred as integer types, permanently stripping leading zeros. Store non-arithmetic identifiers in varchar or char columns.
  • String Truncation Failures: Flat file columns default to a fixed length of 50 characters (DT_STR(50) or varchar(50)). If row 5,000 contains a 75-character product description, the import aborts with diagnostic code 0xC02020A1 (Text was truncated or one or more characters had no match in the target code page). You must manually increase the OutputColumnWidth property in the Flat File Source Advanced settings in addition to sizing the destination SQL Server column.
  • Ambiguous Date and Unicode Parsing: Ambiguous date strings (such as 04/05/2026) can be misinterpreted across regional locales, and UTF-8 files loaded into non-Unicode varchar columns without matching code pages corrupt multi-byte characters. Prefer ISO 8601 date formats (YYYY-MM-DD) and map multi-lingual text to nvarchar (SSIS DT_WSTR) or UTF-8 collated varchar columns.

To prevent data corruption, always load external files into a pre-created staging table first. A staging pattern lets you validate data types, profile anomalies, and check business totals before running a controlled T-SQL MERGE or INSERT...SELECT statement to promote clean rows into production tables. Furthermore, define a clear idempotency strategy: re-running an Append operation after a mid-batch failure will duplicate rows that already committed unless you truncate the staging table first.

Transitioning to Repeatable Imports

In SQL Server Standard, Enterprise, and Developer editions, the wizard provides the option to save the generated workflow as an SSIS package (.dtsx) to either SQL Server (msdb) or the file system. (In SQL Server Express Edition, the wizard can execute the transfer immediately but cannot save the package.)

Keep in mind that a package saved from an interactive desktop session is rarely production-ready out of the box:

  • Security and Execution Identity: An interactive run executes under your personal Windows credentials and accesses local drive letters (such as C:\Imports\data.csv). When scheduled via a SQL Server Agent job step, the package runs under the SQL Server Agent Service Account or a configured SSIS Proxy Account, which requires explicit read permissions on UNC network shares (\\FileServer\Imports\data.csv) and target database permissions.
  • Connection Parameterization: Hard-coded connection strings and sensitive passwords (controlled by the package ProtectionLevel property, such as EncryptSensitiveWithUserKey) will fail when executed by a different user account. Open saved packages in Visual Studio (SSDT) to convert them to the Project Deployment Model, parameterize connection managers, configure logging in SSISDB, and build automated failure notifications.

Check Your Understanding: Troubleshooting Partial Loads

Suppose an import operation completes with warnings or stops mid-execution, resulting in fewer rows in the destination table than exist in the source file. Before re-running the wizard, investigate two primary areas:

  1. Inspect the Execution Report and Transaction Behavior: Check the detailed message log in the wizard progress pane for data conversion errors (such as non-numeric text in a numeric column) or truncation errors. Because the wizard commits rows in batches depending on destination settings, earlier batches may have already committed to the table before the failing row was encountered.
  2. Reconcile Before Re-Running: Do not immediately re-run the wizard with Append rows selected, as doing so will duplicate the rows that succeeded during the first attempt. Instead, inspect the offending source row, adjust the source/destination mapping or fix the source file, TRUNCATE the staging table (or select Delete rows in destination table in the wizard mappings), and execute a clean, full reload.

Microsoft references