datetime2 not supported?

Topics: Possible Bugs/Missing Features?
Nov 10, 2010 at 3:57 PM

Hi guys,

I got some errors in my package earlier:

Error: 0x0 at Update Target, Kimball SCD2: Internal error (Unsupported Data Type for comparison: DT_DBTIMESTAMP2) in ProcessKey, detecting SCD1 and 2 changes in column Audit Deleted DateTime, key: e5260000.

Error: 0x0 at Update Target, Kimball SCD2: Internal error (Internal error (Unsupported Data Type for comparison: DT_DBTIMESTAMP2) in ProcessKey, detecting SCD1 and 2 changes in column Audit Deleted DateTime, key: e5260000.) in ProcessKey detecting changes, key: e5260000.

Error: 0xC0047062 at Update Target, Kimball SCD2 [2623]: System.Exception: Internal exceptions encountered.

at MouldingAndMillwork.SSIS.KimballMethodSCD.ProcessInput(Int32 inputID, PipelineBuffer buffer)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

Error: 0xC0047022 at Update Target, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Kimball SCD2" (2623) failed with error code 0x80131500 while processing input "Existing Dimension" (2629). 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.

Error: 0xC02020C4 at Update Target, Dimension [110]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at Update Target, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Dimension" (110) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0209017 at Update Target, Source [125]: Setting the end of rowset for the buffer failed with error code 0xC0047020.

Error: 0xC0047038 at Update Target, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source" (125) returned error code 0xC0209017. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

The field in question is a datetime2(0) datatype. Any chance of this being allowed. I guess for now I will do a type conversion to and from datetime.

Coordinator
Nov 23, 2010 at 3:42 PM

v1.6 will correctly support datetime2 types...

Jan 26, 2011 at 8:57 PM

Will version 1.6 support the hierarchyid data type?

Coordinator
Feb 22, 2011 at 9:19 PM

I don't believe that SSIS supports that data type, if it doesn't, then no.  If the hierarchy ID can be read by SSIS as a DT_BYTES or something similar, then yes.

Apr 11, 2011 at 1:08 AM

I'm trying v1.6 against SQL Server 2008 R2.

My "SCD Effective Date" and "SCD2 Expiry Date" columns are SQL datatype datetime2 / SSIS Data Type DT_DBTIMESTAMP2.

When I specify them on the "Existing DImension Input Column Definitions" tab, I get this error (marked as a warning in the list at the bottom of the window):

SCD2 Effective Date is not a date type.

 

May 6, 2011 at 3:35 PM

This makes it kind fo tricky to use millisecond granularity under SCD2 date handling given that datetime is not accurate to the millisecond (I think it is 3 milliseconds).  Ideally, we would want to use a DATETIME2(3) columns with this but, I get the same error.

May 24, 2011 at 10:08 AM
MikeHoney wrote:

I'm trying v1.6 against SQL Server 2008 R2.

My "SCD Effective Date" and "SCD2 Expiry Date" columns are SQL datatype datetime2 / SSIS Data Type DT_DBTIMESTAMP2.

When I specify them on the "Existing DImension Input Column Definitions" tab, I get this error (marked as a warning in the list at the bottom of the window):

SCD2 Effective Date is not a date type.

 


We have encountered this same problem. It would appear that the "SCD Effective Date" and "SCD2 Expiry Date" columns do not support SQL datatype datetime2. Can anyone confirm if this is an issue or by design?