Error using the Pivot transformation in MS SQL Server 2005

Question!

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.



Answers

The NULL is causing a problem because you can't set the column name of a table or result set to NULL, which the PIVOT operator is trying to do. You would have to use an ISNULL operator on the pre-pivoted (or unpivoted) data.

ISNULL (,

Usually you'd do something like ISNULL(value, 0).



You can find the naming in the Java Code Conventions.

A quick summary:

  • For classes, use UpperCamelCase.
  • For class members and local variables use lowerCamelCase
  • For packages, use reverse URI, e.g. org.acme.project.subsystem
  • For constants, use ALL_CAPS.
By : jamesh


This video can help you solving your question :)
By: admin