troubleshooting Question

Type of value error in ssis package

Avatar of UnderSeven
UnderSeven asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SSIS
2 Comments1 Solution404 ViewsLast Modified:
I have an ssis execute sql task step

It calls a stored procedure and attempts to dump the output into a user variable of type double.

It is getting a error thrown about not being able to change types mid execution if the result of the stored procedure is zero (I think).

The best way I could troubleshoot this is manually run the stored procedure.  I hard coded the store procedure to act as follows:

declare @array as table(statement float)
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	IF @StatementDocNo = 0
		BEGIN
			SELECT 0 AS [Amount Due]
		END
	ELSE
		BEGIN
			-- Insert statements for procedure here
insert into @array
			SELECT DISTINCT -- select top 10 * from umcr2100
				isnull(util..UMCR2102.umAmount1,0)  AS [Discounted Amount Due]
			FROM
				util..UMCR2100
				INNER JOIN util..UMCR2102 ON util..UMCR2100.umStatementDocNo = util..UMCR2102.umStatementDocNo
			WHERE
				(util..UMCR2100.umStatementDocNo = @StatementDocNo)
				AND
				umDescription1 LIKE 'Total Amount Due%'
		
		IF @@ROWCOUNT = 0
		BEGIN
			SELECT 0 AS [Amount Due]
		END
		ELSE
		BEGIN
		 select * from @array
		END
END

I do not see how it is possible for the code above to throw anything not compatible with a double.  

How can I troubleshoot this/ make this work.

(The output almost always comes out to greater than zero and has a decimal).

This is written in sqlsvr 2008
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros