We help IT Professionals succeed at work.

Type of value error in ssis package

400 Views
Last Modified: 2016-02-10
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

Open in new window


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
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
That was it, once I normalized the output to all come from the same (explicit type) place it worked.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.