I’ve been working strictly with SSIS in SQL Server 2008 for a a little over month now. It’s an amazing tool.

One thing I encounter when generating horizontal tables from adata warehouse is the factthat I have to pivot tables. Last week, when trying to pivot data using the “Pivot Task” I got the error:

“Cannot convert the pivot key value_ to the data type of the pivot key column.” (The underscorereplacesthe name of the output column I had created. )

Thing is, I was trying to be fancy, In my OLE DB Source I had decoded mycolumn names from numbers to text.

This bit me in the end. I couldn’t figure out why I was getting an error. But here was the solution.

Pass the numbers in and name them under your PivotKeyValue.

Correct:

SELECT[1AnchorColumn], [3PivotValue], [2PivotKey]
FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=125 Name=Blankets SourceColumn=LineageID from Input

output column2 “HomeOffice” PivotKeyValue=126 Name=HomeOffice SourceColumn=LineageID fromInput

output column3 “Kitchen” PivotKeyValue=300 Name=Kitchen SourceColumn=LineageID fromInput

Incorrect:


SELECT
[1AnchorColumn], [3PivotValue],[2PivotKey] =
CASE [2PivotKey]
WHEN 125THEN 'Blankets'
WHEN 126 THEN 'HomeOffice'
WHEN 300 THEN 'Kitchen'
ELSE 'Other'END
FROM [AdventureWorksDW].[dbo].[Table]

output column 1 “Blankets” PivotKeyValue=Blankets Name=Blankets SourceColumn=LineageID from Input

output column2 “HomeOffice” PivotKeyValue=HomeOffice Name=HomeOffice SourceColumn=LineageID fromInput

output column3 “Kitchen” PivotKeyValue=Kitchen Name=Kitchen SourceColumn=LineageID fromInput

The logic was that characters were coming from a field that was an integer. Thus the error:

“Cannot convert the pivot key value _ to the data type of the pivot key column”

Hope this helps you in your pivoting woes. I figured it myself! Brian Knight would be proud. ;)

Toodles!

(I really will try to post more often!)

In: Uncategorized | | #