Search code examples
sql-serveroracle-databasessisetl

How to keep trailing space from source to destination using SSIS

Environment

  • Source: Oracle database via OLE DB
  • Destination: SQL Server 2019 via OLE DB
  • Tools: SSIS Visual Studio 2019

Problem

The source has value with space (e.g. 12345 ) but loaded into target database space is gone (e.g. 12345)

I want to keep all spaces in source data and input the same into the target table but cannot find the configuration or any way to keep those spaces.

Solution

  • The problem is data type on target table. Oracle uses VARCHAR2 and CHAR datatypes, and SSIS define them to Unicode string.

    The first time, I declared a column for character with VARCHAR in SQL Server but found extra space missing after ETL. I have changed data type in SQL Server from VARCHAR to NVARCHAR, that can resolve my problem.