SSIS Pivot Transform throwing run-time exception for datetime2 inputs

SSIS Pivot Transform throwing run-time exception for datetime2 inputs by BenAust

Closed

as Not Reproducible Help for as Not Reproducible

Type:	Bug
ID:	558747
Opened:	5/12/2010 4:52:53 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Have reported an issue with SSIS Pivot tranform component on the forum. Check this thread
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a46cb388-d334-43b4-a2dc-70670e5cb77c/

Details (expand)

English

SQL Server 2008 - Developer Edition

Integration Services (DTS)

Windows XP SP2 Professional

US English

1.Create a new SSIS Package.
2. Add a data flow.
3. Add an OLEDB SQL Server datasource and Select a table/query that needs to be pivoted and contains two datetime2 columns as input. Ensure that the table/query has a pivot colum, atleast one set column of datetime2 data type and one pass through column which is of datetime2 data type
4. Add a Pivot Transform to the data flow and connect it to the OLEDB datasource. Define the pivot, set columns and pass through columns. Ensure Pivot Usage of one of the datetime2 column is configured as set column and the other datetime2 column configured as pass through.
5. Connect the Pivot transform to a row count transform and add a data viewer.
6. build and execute the package

the package builds successfully but throws the following exception during run-time

[Pivot [34]] Warning: Not able to convert object

[Pivot [34]] Error: Failure when trying to pivot the source records.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Pivot" (34) failed with error code 0xC020208F while processing input "Pivot Default Input" (35). 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.

Expected result would the pivot transform is able to pivot the datetime2 columns without throwing exception. If I do a data conversion from datetime2 to timestamp data type, I'am able to work around this error. But this means the precision is lost downstream which is significant in the ETL soltion that I'am building.

0 attachments

Hello,

Thanks for reporting this issue.

It looks like it has been fixed in our latest version of the product. I was able to use a column of datatime2 type as a pass-through column in the Pivot transform. The fix should be available in SQL Server Denali version.

Best regards,
Bob Bojanic, SSIS Team