none
The Value Could Not Be Converted Because of a Potential Loss of Data

    Question

  • I've searched the threads and didn't see anything which seemed to fit this specific issue....

     

    I have a Data Flow task which reads from an OLE DB Source (SQL Server 2005), uses a Data Conversion transformation to convert some field values, and finally outputs the result to distinct tabs of an Excel workbook. The task is failing with the following error:

     

    There was an error with input column "oBBCompanyName" (2162) on input "Excel Destination Input" (57). The column status returned was: "The value could not be converted because of a potential loss of data.".

     

    Using the Advanced Editor for the Excel Destination component, I examined the datatype of oBBCompanyName (ID = 2162) in the Input Columns list of the "Excel Destination Input" (identified with ID = 57).  The Data Type is defined as DT_WSTR with Length = 255.  The ExternalMetaDataColumnID = 203.

     

    Also in the Advanced Editor for the Excel Destination, I examined the datatype of BBCompanyName (ID = 203) in the Extranl Columns list of the Excel Destination Input.  The Data Type is defined as "Unicode String [DT_WSTR] with Length = 255.

     

    What could I be overlooking that might be the root cause of this issue?  The same error is occurring for different Excel Destination tasks in the data flow.

     

    Kind regards,

    Orlanzo

    Tuesday, January 29, 2008 3:08 PM

All replies

  • What's the data type on the column that comes out of the Data Conversion component? You can double-click the arrow between the components and go to the Metadata page to see the details.

    Wednesday, January 30, 2008 1:00 AM
  •  

    On the Metadata page, it is listed as DT_WSTR - Length = 255

    ---------------------------------------------------------------------------------------------------------------------

     

    "Name"                "Data Type"        "Precision"          "Scale"  "Length"              "Code Page"      "Sort Key Position"                "Comparison Flags"         "Source Component"

    "BBCompanyName"       "DT_STR"             "0"          "0"          "150"     "1252"   "0"          ""            "Extract 'Summary' data"

    "ODRCompRating"          "DT_STR"             "0"          "0"          "4"          "1252"   "0"          ""            "Extract 'Summary' data"

    "oBBCompanyName"    "DT_WSTR"        "0"          "0"          "255"     "0"          "0"          ""            "Convert Summary Data"

    "oODRCompRating"        "DT_WSTR"        "0"          "0"          "255"     "0"          "0"          ""            "Convert Summary Data"

    Wednesday, January 30, 2008 1:15 AM
  • I've attempted to resolve the issue by recreating the destination tasks and am awaiting the results.  I'll provide an update when after the test.  Here are the OnError messages from the package:

     

    Event Source Start time End time Message
    OnError Extract Data into Excel Template 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError Extract Data into Excel Template 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError MyExtract 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError MyExtract 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError Extract Data into Excel Template 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError MyExtract 1/31/2008 11:25:37 AM 1/31/2008 11:25:37 AM SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00040EDA.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oMROInventory" (1647) on input "Excel Destination Input" (87). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oProduct" (3712) on input "Excel Destination Input" (2352). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oBBCompanyName" (2162) on input "Excel Destination Input" (57). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oMROInventory" (1647) on input "Excel Destination Input" (87). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oProduct" (3712) on input "Excel Destination Input" (2352). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM There was an error with input column "oBBCompanyName" (2162) on input "Excel Destination Input" (57). The column status returned was: "The value could not be converted because of a potential loss of data.".
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (87)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (87)" 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (2352)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (2352)" 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (57)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (57)" 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (87)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (87)" 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (2352)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (2352)" 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (57)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Excel Destination Input" (57)" 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template By Company, Product Tab" (2341) failed with error code 0xC0209029. 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template Summary Tab" (46) failed with error code 0xC0209029. 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template Detail Tab" (76) failed with error code 0xC0209029. 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template By Company, Product Tab" (2341) failed with error code 0xC0209029. 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template Summary Tab" (46) failed with error code 0xC0209029. 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.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Template Detail Tab" (76) failed with error code 0xC0209029. 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.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    OnError Extract Data into Excel Template 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
    OnError MyExtract 1/31/2008 11:25:38 AM 1/31/2008 11:25:38 AM SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread2" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

    Monday, February 04, 2008 3:11 PM
  • Hi Orlanzo,

    You should be use:  LTRIM(RTRIM(oBBCompanyName ))

    Regards, MauricioP.

    • Proposed as answer by BKomm Friday, July 27, 2012 9:21 PM
    Tuesday, February 08, 2011 7:07 PM