Derived Column Error in SSIS I am using the SSIS package to populate data into the DimSuppliers table. However, this throws the following error whenever it reaches the Derived Column Transformation section. 


  [Derived Column [2]] Error: SSIS Error Code
  DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Column" failed
  because error code 0xC0049067 occurred, and the error row disposition
  on "Derived Column.Outputs[Derived Column
  Output].Columns[SupplierFirstName]" specifies failure on error. An
  error occurred on the specified object of the specified component. 
  There may be error messages posted before this with more information
  about the failure.


I am trying to split the ContactName in the source table into



(SupplierFirstName)

SUBSTRING(ContactName,1,FINDSTRING(ContactName,",",1) - 1)


(SupplierLastName) 

 SUBSTRING(ContactName,FINDSTRING(ContactName,",",1) + 1,FINDSTRING(ContactName,",",2) - FINDSTRING(ContactName,",",1) - 1)


I also included this procedure to check the null on the insert values

USE [NorthWindDW]
GO

/****** Object:  StoredProcedure [dbo].[Insert_New_Suppliers]    Script Date: 09/01/2017 23:14:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:      &lt;Author,,Name&gt;
-- Create date: &lt;Create Date,,&gt;
-- Description: &lt;Description,,&gt;
-- =============================================
CREATE PROCEDURE [dbo].[Insert_New_Suppliers] 

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    IF NOT EXISTS 
    (
        SELECT * FROM [dbo].DimSupplier
        WHERE SupplierKey = -1
    )

    BEGIN
    SET IDENTITY_INSERT [dbo].[DimSupplier] ON 
    INSERT [dbo].[DimSupplier] (SupplierKey, SupplierId, SupplierFirstName, SupplierLastName, Position, SupplierCountry, PostCode, Telephone) VALUES
    (-1,-1,'Unknown','Unknown','Unknown', 'Unknown', 'Unknown','Unknowm');
    SET IDENTITY_INSERT [dbo].[DimSupplier] OFF
    END
END


GO


DimSupplier Table 

Create Table [dbo].[DimSupplier]
(SupplierKey         INT IDENTITY NOT NULL,
 SupplierId          INT NOT NULL,
 SupplierFirstName   NVARCHAR(50) NOT NULL,
 SupplierLastName    NVARCHAR(50) NOT NULL,
 Position            NVARCHAR(50) NOT NULL,
 SupplierCountry     NVARCHAR(50) NOT NULL,
 PostCode            NVARCHAR(25) NOT NULL,
 Telephone           NVARCHAR(25) NOT NULL);
Go


Am I doing something wrong?
Joanolo pointed out if the name had a comma which was not in my case. Therefore, I have changed the format of the derived columns expression to the ContactName as follows:

SupplierFirstName 

SUBSTRING(ContactName,1,FINDSTRING(ContactName," ",1) - 1)      


SupplierLastName

SUBSTRING(ContactName,FINDSTRING(ContactName," ",1) + 1,LEN(ContactName) - FINDSTRING(ContactName," ",1))