Error using the Pivot transformation in MS SQL Server 2005

Question

Hi,

I have the following table which I'm pivoting:

(ID, Name, Value)

1, 'Task ID', 123

1, 'Language', 'Java'

2, 'Task ID', 456

I have set the PivotKey to be the Name column, and the PivotedValue to be the Value column. For each of my new columns (Task ID and Language) I have set their PivotKeyValue to be 'Task ID' and 'Language' respectively. My package runs fine with the above data, the result of the pivot operation is:

(ID, Task ID, Language)

1, 123, 'Java'

2, 456, NULL

However, when I run the package on the following source data I have problems:

(ID, Name, Value)

1, NULL, NULL

2, NULL, NULL

The error message I get is:

[Pivot [4511]] Error: The pivot key value "(Can't Convert to String)" is not valid. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Pivot" (4511) failed with error code 0xC0202090. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

I think it means that the pivot operation can't handle the NULL value in the PivotKey column 'Name', because it hasn't been configured to handle the value. Problem is I'm 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 Properties' form)?

Thanks for your time.

Answer 1

I can recreate you initial position with the following script:

CREATE TABLE DataTable(Id INT, Name Nvarchar(40), Value Nvarchar(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 TABLE DataTable

Results:

id TaskID Language 1 123 Java 2 456 NULL

However when I run:

CREATE TABLE DataTable(Id INT, Name Nvarchar(40), Value Nvarchar(40))

INSERT DataTable VALUES(1,NULL,NULL) INSERT DataTable VALUES(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 TABLE DataTable

Results:

id TaskID Language 1 NULL NULL 2 NULL NULL

This executes fine.

I think I must be missing something that you are doing?