When I attempt to run an SSIS stored procedure, I am receiving the following error:

[Execute SQL Task] Error: Executing the query "INSERT INTO tmpSQLXTABSOURCE

..." failed with the following error: "Error converting data type nvarchar to float.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the the particular query that is causing a problem:

SELECT AnalysisSetID, LegalEntityID, FieldID, PeerGroupID, FieldDataSetID, DataSetYear, BCTypeID, BCMnemonic, SUM(CAST(DataValue AS float)) AS DataValue,

DataTable, SystemDataTypeID

FROM dbo.qryXTABData

GROUP BY AnalysisSetID, LegalEntityID, FieldID, PeerGroupID, BCTypeID, DataSetYear, BCMnemonic, DataTable, FieldDataSetID, SystemDataTypeID, DataValue

HAVING (SystemDataTypeID = '{7FD263E9-E684-4C0B-A095-D4273182F295}') AND (AnalysisSetID = '6bac995b-b776-4bf3-bfeb-4264270450f1')

Then when I change it to the following, this particular query works!

SELECT AnalysisSetID, LegalEntityID, FieldID, PeerGroupID, FieldDataSetID, DataSetYear, BCTypeID, BCMnemonic, SUM(CAST(DataValue AS decimal)) AS DataValue,

DataTable, SystemDataTypeID

FROM dbo.qryXTABData

GROUP BY AnalysisSetID, LegalEntityID, FieldID, PeerGroupID, BCTypeID, DataSetYear, BCMnemonic, DataTable, FieldDataSetID, SystemDataTypeID, DataValue

HAVING (SystemDataTypeID = '{7FD263E9-E684-4C0B-A095-D4273182F295}') AND (AnalysisSetID = '6bac995b-b776-4bf3-bfeb-4264270450f1')

Does anyone have any idea as to why using the cast to float would fail but the cast to decimal succeeds?

Then I try to update the appropriate view in SQL Server, but when I attempt to run the entire code from the stored procedure, I receive the following error:

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__tmpSQLXT__2C3AA4296EAEBD9A'. Cannot insert duplicate key in object 'dbo.tmpSQLXTABSOURCE'.

The statement has been terminated.

Does anyone have any suggestions?

TIA.