Converting Currency Data to Numeric while retaining NULLs in SSIS

Step 1: Create Sample Excel, which we will use as source.

Step 2: Prepare Destination Table.

• CREATE TABLE [dbo].[Destination](
      [ID] [float] NULL,
      [Name] [nvarchar](255) NULL,
      [Amount-Numeric] [numeric](12, 2) NULL
  ) ON [PRIMARY]

Step 3: Now Create New SSIS Solution and Add New Package as shown in screens below.

Approach 1:

•  Data Flow Task

• Derived Column

• Result

Approach 2:

• Data Flow Task

 

• Conditional Split - Splitting Null and Not Null records.

•  Derived Column - Removing $ sign from not null records.

•  Union All - Combining Null and Not Null records.

• Result