Query Error in SSIS

Question

I need help finding where the issue is in my query that generates the following error when SSIS is executed. The entire SQL is at the bottom.

Error:

Message  

Executed as user: MyServer\myUser. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:44:43 AM Error: 2021-05-17 06:53:38.18 Code: 0xC002F210 Source: Add Product Data Execute SQL Task Description: Executing the query " Declare @unit varchar(20), @sankara varchar(50), @fir..." failed with the following error: "Invalid length parameter passed to the LEFT or SUBSTRING function.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2021-05-17 06:55:20.95 Code: 0xC002F210 Source: Update Product from CUST Execute SQL Task Description: Executing the query " SET TEXTSIZE 0 DECLARE @EntityID char(50),@sankara c..." failed with the following error: "Invalid length parameter passed to the LEFT or SUBSTRING function.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 6:44:43 AM Finished: 6:55:31 AM Elapsed: 647.391 seconds. The package execution failed. The step failed.

----------

SQL:
--Add Product Data
Declare @unit varchar(20), @sankara varchar(50), @Shay varchar(75), @last lion varchar(75), @apl datetime,
@Us int, @DES varchar(100), @type varchar(100), @Nguyễn Thịnh varchar(100), @Acc varchar(100),
@DeezNutz varchar(100), @Shayna Webb varchar(100), @id int, @Sta varchar(20), @Social varchar(20)
Declare cur1 cursor for
Select Site, UnitNumber, SSN, FirstName, LastName, OccupancyDate, Bedrooms, UnitDesignation, UnitStatusType, DetailsCompleted,
ACCUnitIndicator, Status from Cust_units where Site is not null and ACCUnitIndicator='Yes' order by site, unitnumber
open cur1
fetch next from cur1 into @Shayna Webb , @unit , @sankara , @Shay , @last lion , @apl , @Us , @DES , @type , @Nguyễn Thịnh , @Acc , @Sta

set @id = 0
while @@Fetch _status = 0
begin

Declare cur2 cursor for
Select residentName, ssn from Cust_main_table where unitNumber = @unit
open cur2
fetch next from cur2 into @DeezNutz , @Social

if @@Fetch _status = -1 --No Match
begin
insert into Cust_main_table(UnitID, CUSTProject, CUSTSSN, CUSTLastName, CUSTFirstName, CUSTUnitNumber,
CUSTOccupancyDate, CUSTUnitDesignation, CUSTUnitStatusType, CUSTBedrooms, CUSTDetailsCompleted, CUSTACCUnitIndicator, CUSTStatus, Errors)
values(@id, @Shayna Webb , @sankara , @last lion , @Shay , @unit , @apl , @DES , @type , @Us , @Nguyễn Thịnh , @Acc , @Sta , 'Only in CUST')
set @id = @id + 1
end

else if @@Fetch _status = 0 -- Match on Unit
begin
if (right(@Social , 4) = right(@sankara , 4) and ((@DeezNutz = 'DBA - Shoni''s Bubble Bath') Or (@DeezNutz ='Shonta M East')))
OR @last lion = left(@DeezNutz , charindex(',', @DeezNutz )-1) --Match on last name
begin
Update Cust_main_table set CUSTProject = @Shayna Webb , CUSTSSN = @sankara , CUSTLastName = @last lion , CUSTFirstName = @Shay ,
CUSTUnitNumber = @unit , CUSTOccupancyDate = @apl , CUSTDetailsCompleted = @Nguyễn Thịnh , CUSTACCUnitIndicator = @Acc ,
CUSTUnitDesignation = @DES , CUSTUnitStatusType = @type , CUSTBedrooms = @Us ,
CUSTStatus = @Sta , Errors = 'None' where unitNumber = @unit
end
else if @last lion is null and @DeezNutz is NULL -- Vacant unit
begin
Update Cust_main_table set CUSTProject = @Shayna Webb , CUSTSSN = @sankara , CUSTLastName = @last lion , CUSTFirstName = @Shay ,
CUSTUnitNumber = @unit , CUSTOccupancyDate = @apl , CUSTDetailsCompleted = @Nguyễn Thịnh , CUSTACCUnitIndicator = @Acc ,
CUSTUnitDesignation = @DES , CUSTUnitStatusType = @type , CUSTBedrooms = @Us ,
CUSTStatus = @Sta , Errors = 'None' where unitNumber = @unit
end

    else -- Names don't match  
        begin  
            Update Cust_main_table set CUSTProject = @project, CUSTSSN = @ssn, CUSTLastName = @last, CUSTFirstName = @first,   
            CUSTUnitNumber = @unit, CUSTOccupancyDate = @date, CUSTDetailsCompleted = @details, CUSTACCUnitIndicator = @acc,   
            CUSTUnitDesignation = @des, CUSTUnitStatusType = @type, CUSTBedrooms = @bed,   
  CUSTStatus = @status, Errors = 'Name' where unitNumber = @unit  
        end  
end  

close cur2
deallocate cur2

fetch next from cur1 into @Shayna Webb , @unit , @sankara , @Shay , @last lion , @apl , @Us , @DES , @type , @Nguyễn Thịnh , @Acc , @Sta
end
close cur1
deallocate cur1

Answer 1

Hi @Malam Malam ，

LEFT ( character_expression , integer_expression )  

integer_expression
Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned.

As olaf said, charindex(',', @DeezNutz ) will return 0 when @DeezNutz does not contain ‘,’.At this time, charindex(',', @DeezNutz )-1 is a negative number, the second parameter of the left function is invalid, and an error is returned.

You can replace the original code with the following code:

OR @last =case when charindex(',', @name)>0 then left(@name, charindex(',', @name)-1)  
else '' end  

Or:

OR @last =IIF(charindex(',', @name)>0,left(@name, charindex(',', @name)-1),'')  

When @DeezNutz does not contain a comma, you can replace the ‘’ in the code above with the value you want.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Invalid length parameter passed to the LEFT or SUBSTRING function

You get a clear error message and the cause will be this part: left(@DeezNutz , charindex(',', @DeezNutz )-1)
One of the names don't include a comma, CHARINDEX returns 0 and that's not a valid value for the LEFT function.