August 10, 2010 at 4:06 am
I have a package that checks to verify that a value has been added to our database before it runs the package. If the value doesn't exist, an email is sent out saying it doesn't. If it does exist, than the package continues to process.
The package has been running fine, using BOTH scenarios, for over a year. Until today. Today, it decides to error out because of a datatype issue. So far as I can tell, none of the columns have changed in the table either.
Here's what I've got.
IF EXISTS (SELECT [Name] FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[InterestRate]')
AND type in (N'U'))
DROP TABLE [dbo].[InterestRate];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[InterestRate](
[RateDate] [datetime] NOT NULL,
[InterestRate] [float] NULL,
CONSTRAINT [IDX_tblIntRate_RateDate]
PRIMARY KEY CLUSTERED
(
[RateDate] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF,
FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY];
--FYI: non-essential columns are excluded from this code.
GO
SET ANSI_PADDING OFF;
GO
INSERT INTO dbo.InterestRate (RateDate,InterestRate)
(SELECT '06/30/2010', 0.0016
UNION ALL
SELECT '05/31/2010', 0.0016
UNION ALL
SELECT '04/30/2010', 0.0015
UNION ALL
SELECT '03/31/2010', 0.0016
UNION ALL
SELECT '02/28/2010', 0.0014);
In my SSIS package, I have a variable called IntRate set as Double, ResultSet set as single row, and the variable appropriately set up in the task to receive the result set. Here's the T-SQL Task code.
DECLARE @IntRate decimal, @FileDate datetime,
@FileDate2 varchar(10), @RateDate datetime;
SET NOCOUNT ON
BEGIN
SELECT TOP 1 @FileDate = ReportDate
FROM Staging_Producer_Experience;
Select Top 1 @RateDate = RateDate from dbo.InterestRate
Order by RateDate Desc;
SET @FileDate2 =Left(CONVERT(varchar(10),@FileDate,1),2)
+ Right(CONVERT(varchar(10),@FileDate,1),2);
If Left(CONVERT(varchar(10),@ratedate,1),2)
+ Right(CONVERT(varchar(10),@ratedate,1),2) = @FileDate2
Begin
SELECT ISNULL(InterestRate, 0) AS IntRate
FROM dbo.InterestRate
WHERE Left(CONVERT(varchar(10),ratedate,1),2)
+ Right(CONVERT(varchar(10),ratedate,1),2) = @FileDate2
END
Else Select 0
End
Now there is no entry for July. So my step should be processing normally and going to the SendEmail task to notify everyone that there's no interest rate. This process has worked fine before. But today, it's failing with the error:
Error: 2010-08-10 03:31:21.35
Code: 0xC001F009
Source: APACS_GapRetro_ImportXycor
Description: The type of the value being assigned to variable "User::IntRate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
End Error
I've changed the T-SQL result to .0 in an attempt to run the step correctly, but that doesn't work. There is no other datatype in SSIS to use for this, and the code works fine in SSMS. So I'm seriously confused. Any thoughts?
August 10, 2010 at 4:32 am
Very weird. Perhaps you could debug the package by changing the variable's type to Object to get over this error and then inspecting its contents...
August 10, 2010 at 5:37 am
Hmm. I didn't think about trying Object. I'll give that a shot.
But I'm not mistaken, am I, that 0 as a value should work fine in a Double datatype? And that Decimal (from the table) should convert to Double (the variable) without a problem?
August 10, 2010 at 6:01 am
Brandie Tarvin (8/10/2010)
Hmm. I didn't think about trying Object. I'll give that a shot.But I'm not mistaken, am I, that 0 as a value should work fine in a Double datatype? And that Decimal (from the table) should convert to Double (the variable) without a problem?
I agree - should all work fine without any explicit casting.
August 10, 2010 at 6:13 am
Brandie
Is this the first time that setting the rate to zero has been invoked? What happens if you set it to a decimal rather than an integer, like this?
SELECT ISNULL(InterestRate, 0.0000) AS IntRate
John
August 10, 2010 at 6:55 am
No, the setting to zero has been invoked before and worked fine. That's why I don't understand why it's failing now.
I've tried .0 and 0.0 this morning and both failed. Using Object works for that single step, but I'd need to test the entire package and send it through our SDLC before I could make that change. I'd rather find out why something that worked before isn't working now.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply