Error using Pivot transform in MS SQL Server 2005
I have the following table that I rotate:
(id, name, value)
1, 'task ID, 123
1, 'Language', 'Java'
2, 'task ID, 456
I have set PivotKey as Name column and PivotedValue as Value column. For each of my new columns (task ID and language), I set their PivotKeyValue as "task ID" and "language" respectively. My package works fine with the above data, the result of the rotation operation is:
(id, task id, language)
1, 123, 'Java'
2, 456, NULL
However, when I run the package on the following raw data, I have problems:
(id, name, value)
1, NULL, NULL
2, NULL, NULL
The error message I am getting:
[Pivot [4511]] Error: Keykey value "(Can not Convert to String)" is incorrect. [DTS.Pipeline] Error: SSIS error code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on the Pivot component (4511) failed with error code 0xC0202090. The selected component returned an error from the ProcessInput method. The error is component specific, but the error is fatal and will cause the data flow task to terminate. Error messages can be submitted prior to this with additional information about the failure.
I think this means that the pivot operation cannot handle a NULL value in the "Name" PivotKey column as it is not configured to handle a value. The problem is I am not sure how to do this. How do you detect and deal with a NULL value in the PivotKeyValue property of an output column (input and output form)?
Thank you for your time.
I can recreate the original position with the following script:
CREATE TABLE DataTable (Id INT, Name Nvarchar (40), Nvarchar value (40))
INSERT DataTable VALUES (1, 'TaskID', '123') INSERT DataTable VALUES (1, 'Language', 'Java') INSERT DataTable VALUES (2, 'TaskID', '456')
SELECT id, [TaskID], [Language] FROM (SELECT ID, [Name], [Value] FROM DataTable) AS s PIVOT (MAX (value) FOR [Name] IN ([TaskID], [Language])) AS p
DROP DataTable
Results:
id Language TaskID 1 123 Java 2 456 NULL
However, when I run:
CREATE TABLE DataTable (Id INT, Name Nvarchar (40), Nvarchar value (40))
INSERT VALUES DataTable (1, NULL, NULL) INSERT VALUES DataTable (2, NULL, NULL)
SELECT id, [TaskID], [Language] FROM (SELECT ID, [Name], [Value] FROM DataTable) AS s PIVOT (MAX (value) FOR [Name] IN ([TaskID], [Language])) AS p
DROP DataTable
Results:
id Language TaskID 1 NULL NULL 2 NULL NULL
This works great.
Think I should be missing something, what are you doing?