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), @ssn 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),@SSN 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), @ssn varchar(50), @first varchar(75), @last varchar(75), @date datetime,
@bed int, @des varchar(100), @type varchar(100), @details varchar(100), @acc varchar(100),
@name varchar(100), @project varchar(100), @id int, @status 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 @project, @unit, @ssn, @first, @last, @date, @bed, @des, @type, @details, @acc, @status
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 @name, @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, @project, @ssn, @last, @first, @unit, @date, @des, @type, @bed, @details, @acc, @status, 'Only in CUST')
set @id = @id + 1
end
else if @@fetch_status = 0 -- Match on Unit
begin
if (right(@social, 4) = right(@ssn, 4) and ((@name = 'DBA - Shoni''s Bubble Bath') Or (@name ='Shonta M East')))
OR @last = left(@name, charindex(',', @name)-1) --Match on last name
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 = 'None' where unitNumber = @unit
end
else if @last is null and @name is NULL -- Vacant unit
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 = '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 @project, @unit, @ssn, @first, @last, @date, @bed, @des, @type, @details, @acc, @status
end
close cur1
deallocate cur1