question

MalamMalam-4042 avatar image
0 Votes"
MalamMalam-4042 asked JeffreyWilliams-3310 commented

Query Error in SSIS

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






sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered JeffreyWilliams-3310 commented

Hi @MalamMalam-4042

 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(',', @name) will return 0 when @name does not contain ‘,’.At this time, charindex(',', @name)-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 @name 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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If we assume the @name without a column is a last name and no first name - then we would want to return that value. To do that use:

 left(@name, charindex(',', concat(@name, ','))-1)

This will make sure there is always at least 1 comma - at the end of the string.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

Invalid length parameter passed to the LEFT or SUBSTRING function

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.