Peculiar problem with dts/sp

[rtgordon]

I have a dts package that reads an excel file into a temp table. When I execute the package, it loads the excel data into the db table fine, then it executes a sp to verify/move the data. It runs for a few seconds and returns complete. All green, but when I query the database, only a small number get loaded (the first 50-70. Here is why it is peculiar. If I run it in query analylzer (via exec spMyStoredProc), it doesn't have any problems (it's just dog slow!)

here is my stored proc

CREATE PROCEDURE dbo.spExcelImport AS
SET NOCOUNT ON

--Clear empty spreadsheet rows from the upload table.
DELETE FROM PSDBT1.dbo.ExcelRawUpload 
	WHERE CommCode IS NULL AND Family IS NULL AND Type IS NULL AND SizeOfPart IS NULL AND Vend IS NULL AND PartNumber IS NULL AND TC IS NULL AND ValueOfCapacitance IS NULL AND Volt IS NULL AND Tol IS NULL AND Term IS NULL AND Pack IS NULL AND FR IS NULL AND part_id IS NULL AND ErrorOnImport IS NULL
--Record error for any row that has any column imported with NULLs.
UPDATE PSDBT1.dbo.ExcelRawUpload 
	SET ErrorOnImport='A NULL for one of the following forbids an INSERT into the Parameters table: Commodity Code, Family, Type, Size of Part, Vendor, Part Number, TC, Value of Capacitance, Voltage, Tolerance, Term Style.' 
	WHERE CommCode IS NULL OR Family IS NULL OR Type IS NULL OR SizeOfPart IS NULL OR Vend IS NULL OR PartNumber IS NULL OR TC IS NULL OR ValueOfCapacitance IS NULL OR Volt IS NULL OR Tol IS NULL OR Term IS NULL

--Declare variables.
DECLARE @CommCode varchar(20), @Family varchar(20), 
	@Type varchar(20), @SizeOfPart varchar(20), 
	@Vend varchar(20), @PartNumber varchar(50), 
	@TC varchar(20), @ValueOfCapacitance varchar(20), 
	@Volt varchar(20), @Tol varchar(20), 
	@Term varchar(20), @Pack varchar(20), 
	@FR varchar(20), @part_id int, 
	@ErrorOnImport varchar(200), @RawUploadID int,
	@error_var int, @rowcount_var int

--Declare and open cursor.
DECLARE Excel_cursor CURSOR FOR
	SELECT CommCode, Family, Type, SizeOfPart, Vend, PartNumber, TC, ValueOfCapacitance, Volt, Tol, Term, Pack, FR, part_id, RawUploadID
	FROM PSDBT1.dbo.ExcelRawUpload
	WHERE CommCode IS NOT NULL AND Family IS NOT NULL AND Type IS NOT NULL AND SizeOfPart IS NOT NULL AND Vend IS NOT NULL AND PartNumber IS NOT NULL AND TC IS NOT NULL AND ValueOfCapacitance IS NOT NULL AND Volt IS NOT NULL AND Tol IS NOT NULL AND Term IS NOT NULL AND 
	ErrorOnImport IS NULL
OPEN Excel_cursor

--Get the first row.
FETCH NEXT FROM Excel_cursor INTO @CommCode, @Family, @Type, @SizeOfPart, 
	@Vend, @PartNumber, @TC, @ValueOfCapacitance, @Volt, @Tol, @Term, 
	@Pack, @FR, @part_id, @RawUploadID

--Loop until end of recordset.
WHILE @@FETCH_STATUS = 0
    BEGIN
	IF @Pack = 'B' SET @Pack = 'BULK'
	
	IF @SizeOfPart IS NOT NULL AND ISNUMERIC(Left(@SizeOfPart,1))=0 
		SET @SizeOfPart = Right(@SizeOfPart, Len(@SizeOfPart)-1)

	SET @part_id = (SELECT TOP 1 part_id 
	FROM PSDBT1.dbo.psdb_cap_parameters 
	WHERE commodity_cd = @CommCode AND 
	      family = @Family AND 
	      component_type_cd = @Type AND 
	      part_size = @SizeOfPart AND 
	      temp_coeff = @TC AND 
	      cap_value = @ValueOfCapacitance AND 
	      voltage = @Volt AND 
	      cap_tolerance = @Tol AND 
	      term_style = @Term)

	--If there was no model row in the Parametrics table, INSERT one.
	IF @part_id IS NULL 
	    BEGIN
	        INSERT INTO PSDBT1.dbo.psdb_cap_parameters 
		(insert_date, commodity_cd, family, component_type_cd, part_size, temp_coeff, cap_value, voltage, cap_tolerance, term_style, packaging, failure_rate) 
	        VALUES 
		(GetDate(), @CommCode, @Family, @Type, @SizeOfPart, @TC, @ValueOfCapacitance, @Volt, @Tol, @Term, @Pack, @FR)

		--Record to ExcelRawUpload any errors during Parametrics table INSERT.
		SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
		IF @error_var <> 0 OR @rowcount_var = 0 
		    BEGIN
			SET @ErrorOnImport = 'The INSERT into the Parameters table failed, possibly for Type violations.'
			UPDATE PSDBT1.dbo.ExcelRawUpload SET ErrorOnImport=@ErrorOnImport WHERE RawUploadID=@RawUploadID
		    END
	    SET @part_id = (SELECT TOP 1 part_id 
		FROM PSDBT1.dbo.psdb_cap_parameters 
		WHERE commodity_cd = @CommCode AND 
		      family = @Family AND 
		      component_type_cd = @Type AND 
		      part_size = @SizeOfPart AND 
		      temp_coeff = @TC AND 
		      cap_value = @ValueOfCapacitance AND 
		      voltage = @Volt AND 
		      cap_tolerance = @Tol AND 
	                    term_style = @Term)
	    END
	--If a model row exists in the Parametrics table, use its part_id key to INSERT into the Details table.
		--INSERT only if Part Number doesn't already exist.
		SELECT rel_part_num FROM PSDBT1.dbo.psdb_cap_part_dtl WHERE rel_part_num = @PartNumber AND mfg_id = @Vend
		SELECT @rowcount_var = @@ROWCOUNT
		IF @rowcount_var=0
		    BEGIN
			INSERT INTO PSDBT1.dbo.psdb_cap_part_dtl 
			(part_id, rel_part_num, insert_date, mfg_id, alias_ind) 
			VALUES 
			(@part_id, @PartNumber, GetDate(), @Vend, 'N') 
			SELECT @error_var = @@ERROR
		    END
		ELSE
		    BEGIN
			SET @error_var = 599
		    END
			
		--Record to ExcelRawUpload any errors during Detail table INSERT.
		IF @error_var <> 0
		    BEGIN
		    IF @error_var <> 599
		    	BEGIN
			SET @ErrorOnImport = 'The INSERT into the Details table failed, possibly for Type violations.'
			END
		    ELSE
			BEGIN
			SET @ErrorOnImport = 'The INSERT into the Details table failed, part exists.'	
			END
		    UPDATE PSDBT1.dbo.ExcelRawUpload SET ErrorOnImport=@ErrorOnImport WHERE RawUploadID=@RawUploadID
		    END
    --Get the next row.
    FETCH NEXT FROM Excel_cursor INTO @CommCode, @Family, @Type, @SizeOfPart, 
	@Vend, @PartNumber, @TC, @ValueOfCapacitance, @Volt, @Tol, @Term, 
	@Pack, @FR, @part_id, @RawUploadID
    END

CLOSE Excel_cursor
DEALLOCATE Excel_cursor

--Clean up the upload table to leave only failed rows.
DELETE FROM PSDBT1.dbo.ExcelRawUpload 
	WHERE ErrorOnImport IS NULL

SET NOCOUNT OFF

Thanks to anyone that helps.
gordon

 

[rtgordon]

anyone???

I am sure it is something simple that I am missing because it works fine in query analyzer, but just stops (after processing a few records) without returning any errors when I try and execute it through dts.

gordon