0

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

enter image description here

(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:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
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?

  • 1
    You're assuming your suppliers work with 'name, name'. Might you have some supplier without a comma? – joanolo Jan 10 '17 at 0:41
  • See also – billinkc Jan 10 '17 at 14:35
2

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))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.