locked
Failed to retrieve long data for column "DataFile" RRS feed

  • Question

  • Hi ,

    I am facing below error while executing SSIS Package using SQL Agent Job

    Description: Failed to retrieve long data for column "DataFile".    Code: 0xC020901C   

    The column status returned was: "DBSTATUS_UNAVAILABLE".  

    Where DataFile column datatype is XML(.). the column contains XML data. its huge.

    i read some of the blogs and used file shares where huge space for BlobTempStoragePath and BufferTempStoragePath.  even after configuring the flash drives i am facing the same issue. 

    can any body suggest what might be the issue.

    Thanks

    Tuesday, September 29, 2015 9:01 PM

All replies

  • I think you just hit the limits of the datatype.

    Share technical details on the implementation, may there is a way of fixing it.


    Arthur

    MyBlog


    Twitter

    Tuesday, September 29, 2015 9:23 PM
  • Hi Raj,

    Can you let us know at which transformation it is failing is it in OLE DB DESTINATION?

    How many rows do you have, Could you just test it with one row. Do u still face this issue?


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Tuesday, September 29, 2015 9:27 PM
  • Hi Arthur,

    Package is pulling Data from a table and Writing to a flat file. 

    Table (Source)--> Extract and Transform --> Table (Stage) --> Load --> Flat file.

    Source Table Schema is


    Column_name Type
    N_TxMessage_Outbound bigint
    G_TransactionID uniqueidentifier
    C_TxType nvarchar
    C_SourceType varchar
    X_Data xml
    X_CorrKeys xml
    C_CycleID varchar
    C_TxStatus char
    N_TxSequence int
    N_TxSequenceGroup varchar
    N_TxPriority int
    B_Rcd_Del bit
    D_CreationDate datetime
    C_CreatedBy varchar
    D_LastModifiedDate datetime


    package is failing while extracting the X_Data Column.

    Ans We have Two environments, where the package is running. Its running successfully in one Env which is of 64Gigs but failing in env which is of 256 gigs.

    Wednesday, September 30, 2015 2:14 PM
  • Hi Kamalesh,

    Below is the flow.

    Table (Source)--> Extract and Transform --> Table (Stage) --> Load --> Flat file.

    yes it is OLE DB DESTINATION.

    Thanks,

    Raj

    Wednesday, September 30, 2015 2:21 PM
  • Raj, Have you tried with one single records. 

    Please do so and let us know the result.


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Wednesday, September 30, 2015 5:36 PM










  • X_Data xml
    X_CorrKeys xml
    C_CycleID varchar
    C_TxStatus char
    N_TxSequence int
    N_TxSequenceGroup varchar
    N_TxPriority int
    B_Rcd_Del bit
    D_CreationDate datetime
    C_CreatedBy varchar
    D_LastModifiedDate datetime


    package is failing while extracting the X_Data Column.

    Its running successfully in one Env which is of 64Gigs but failing in env which is of 256 gigs.

    What is the difference between the environments?

     I suggest you also look at the patch level if any SPs are amiss.


    Arthur

    MyBlog


    Twitter

    Wednesday, September 30, 2015 5:45 PM
  • Hi Kamalesh

    Yeah, i tried with one record. even then its throwing the same error.

    below is the full error description.

    Source: DFT - Load From Transaction from Integration to Staging      Description:   End Error  Error: 2015-10-01 10:03:09.02     Code: 0x80004005     Source: DFT - Load From Transaction from Integration to Staging      Description:   End Error  Error: 2015-10-01 10:03:09.06     Code: 0xC0208266     Source: DFT - Load From Transaction from Integration to Staging      Description:   End Error  Error: 2015-10-01 10:03:09.11     Code: 0xC0208265     Source: DFT - Load From Transaction from Integration to Staging OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim [51]     Description: Failed to retrieve long data for column "X_Data".  End Error  Error: 2015-10-01 10:03:09.16     Code: 0xC020901C     Source: DFT - Load From Transaction from Integration to Staging OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim [51]     Description: There was an error with OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim.Outputs[OLE DB Source Output] on OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim. The column status returned was: "DBSTATUS_UNAVAILABLE".  End Error  Error: 2015-10-01 10:03:09.20     Code: 0xC0209029     Source: DFT - Load From Transaction from Integration to Staging OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim [51]     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim.Outputs[OLE DB Source Output]" failed because error code 0xC0209071 occurred, and the error row disposition on "OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2015-10-01 10:03:09.25     Code: 0xC0047038     Source: DFT - Load From Transaction from Integration to Staging SSIS.Pipeline     Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OLEDB_SRC Integration DB (TxMessage_Outbound)  Update Claim returned error code 0xC0209029.  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.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:03:02 AM  Finished: 10:03:09 AM  Elapsed:  6.485 seconds.  The package execution failed.  The step failed.

    Thursday, October 1, 2015 2:04 PM
  • Hi Arthur,

    Successfully Running on version - 

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
    Oct 19 2012 13:38:57 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

    failing on Version -

    Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
    May 14 2014 18:34:29 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


    Thursday, October 1, 2015 2:09 PM
  • Apply SP1

    Arthur

    MyBlog


    Twitter

    Thursday, October 1, 2015 3:35 PM
  • Raj,

    I am agree with ArthurZ, Apply SP1 and that would be better.


    Thanks, If my reply is helpful please mark as answer or vote as helpful.

    Thursday, October 1, 2015 7:07 PM