SSIS Script Component-> Unicode data is odd byte size for column ...

Question

I don't seem to be able to find a way around this error, nor figure out what the "real" issue is:

[Destination - CONT_DATAPULL_SF [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unicode data is odd byte size for column 16. Should be even byte size.".

(done this without issue on several tables, structured similarly, without hitting this...)

Here's my code (and everything in the data viewers look 100% good/expected):

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ID = Row.IDx.Trim('"', ' ', '\t');
Row.CONTRACTNUMBER = Row.CONTRACTNUMBERx.Trim('"', ' ', '\t');

    if (!Row.SMALLCOMMERCIALTARGETLISTPREMIUMCx_IsNull && Row.SMALLCOMMERCIALTARGETLISTPREMIUMCx.Length > 0)
    {
        Row.SMALLCOMMERCIALTARGETLISTPREMIUMC = Int32.Parse(Row.SMALLCOMMERCIALTARGETLISTPREMIUMCx.Trim('"', ' ', '\t').Substring(0, Row.SMALLCOMMERCIALTARGETLISTPREMIUMCx.Trim('"', ' ', '\t').IndexOf('.')));
    }

    if (!Row.SMALLCOMMERCIALTARGETLISTPIFCx_IsNull && Row.SMALLCOMMERCIALTARGETLISTPIFCx.Length > 0)
    {
        Row.SMALLCOMMERCIALTARGETLISTPIFC = Int32.Parse(Row.SMALLCOMMERCIALTARGETLISTPIFCx.Trim('"', ' ', '\t').Substring(0, Row.SMALLCOMMERCIALTARGETLISTPIFCx.Trim('"', ' ', '\t').IndexOf('.')));
    }

    Row.ASSIGNEDCONSOLIDATIONUNDERWRITERC = Row.ASSIGNEDCONSOLIDATIONUNDERWRITERCx.Trim('"', ' ', '\t');

    if (!Row.BTFAVAILABLEINTHESTATECx_IsNull && Row.BTFAVAILABLEINTHESTATECx.Length > 0)
    {
        byte[] copyData = Row.BTFAVAILABLEINTHESTATECx.GetBlobData(0, checked((Int32)Row.BTFAVAILABLEINTHESTATECx.Length));
        Row.BTFAVAILABLEINTHESTATEC.AddBlobData(copyData, checked((Int32)Row.BTFAVAILABLEINTHESTATECx.Length));
    }

    if (!Row.DESCRIBEPRICINGPARAMETERSCx_IsNull && Row.DESCRIBEPRICINGPARAMETERSCx.Length > 0)
    {
        byte[] copyData = Row.DESCRIBEPRICINGPARAMETERSCx.GetBlobData(0, checked((Int32)Row.DESCRIBEPRICINGPARAMETERSCx.Length));
        Row.DESCRIBEPRICINGPARAMETERSC.AddBlobData(copyData, checked((Int32)Row.DESCRIBEPRICINGPARAMETERSCx.Length));
    }

    try
    {
        if (!Row.EXTERNALKICKOFFCx_IsNull && Row.EXTERNALKICKOFFCx.Length >= 10)
        {
            Row.EXTERNALKICKOFFC = DateTime.Parse(Row.EXTERNALKICKOFFCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.EXTERNALKICKOFFCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length) + "T00:00:00.000Z").ToUniversalTime();
        }
        else  //set the EXTERNALKICKOFFC equal to CREATEDDATE  (SSIS is a mess and does NOT understand how to handle NULLs with COM objects)
        {
            Row.EXTERNALKICKOFFC = (DateTime)DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
    }
    catch (Exception)
    {
    }

    try
    {
        if (!Row.FIRSTPARTNERSHIPCALLCx_IsNull && Row.FIRSTPARTNERSHIPCALLCx.Length >= 10)
        {
            Row.FIRSTPARTNERSHIPCALLC = DateTime.Parse(Row.FIRSTPARTNERSHIPCALLCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.FIRSTPARTNERSHIPCALLCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length) + "T00:00:00.000Z").ToUniversalTime();
        }
        else  //set the FIRSTPARTNERSHIPCALLC equal to CREATEDDATE  (SSIS is a mess and does NOT understand how to handle NULLs with COM objects)
        {
            Row.FIRSTPARTNERSHIPCALLC = (DateTime)DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
    }
    catch (Exception)
    {
    }

    Row.FREQUENCYOFPARTNERSHIPCALLC = Row.FREQUENCYOFPARTNERSHIPCALLCx.Trim('"', ' ', '\t');

    try
    {
        if (!Row.STARTDATEOFTHECONSOLIDATIONCx_IsNull && Row.STARTDATEOFTHECONSOLIDATIONCx.Length >= 10)
        {
            Row.STARTDATEOFTHECONSOLIDATIONC = DateTime.Parse(Row.STARTDATEOFTHECONSOLIDATIONCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.STARTDATEOFTHECONSOLIDATIONCx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length) + "T00:00:00.000Z").ToUniversalTime();
        }
        else  //set the STARTDATEOFTHECONSOLIDATIONC equal to CREATEDDATE  (SSIS is a mess and does NOT understand how to handle NULLs with COM objects)
        {
            Row.STARTDATEOFTHECONSOLIDATIONC = (DateTime)DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
    }
    catch (Exception)
    {
    }

    Row.CREATEDBYID = Row.CREATEDBYNAMECx.Trim('"', ' ', '\t');

    try
    {
        if (!Row.CREATEDDATEx_IsNull && Row.CREATEDDATEx.Length >= 24)
        {
            Row.CREATEDDATE = DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
        else //this is a HARD error, there must be a valid CREATEDDATE, crash the package...
        {
            Row.CREATEDDATE = DateTime.MinValue;
        }
    }
    catch (Exception)  //this is a HARD error, there must be a valid CREATEDDATE, crash the package...
    {
        Row.CREATEDDATE = DateTime.MinValue;
    }

    Row.LASTMODIFIEDBYID = Row.LASTMODIFIEDBYNAMECx.Trim('"', ' ', '\t');

    try
    {
        if (!Row.LASTMODIFIEDDATEx_IsNull && Row.LASTMODIFIEDDATEx.Length >= 24)
        {
            Row.LASTMODIFIEDDATE = DateTime.Parse(Row.LASTMODIFIEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.LASTMODIFIEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
        else  //set the LASTMODIFIEDDATE equal to CREATEDDATE  (SSIS is a mess and does NOT understand how to handle NULLs with COM objects)
        {
            Row.LASTMODIFIEDDATE = DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
        }
    }
    catch (Exception)
    {
        Row.LASTMODIFIEDDATE = (DateTime)DateTime.Parse(Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Substring(0, Row.CREATEDDATEx.Trim('"', ' ', '\t').TrimEnd('\r', '\n').Length)).ToUniversalTime();
    }

    if (!Row.OPPORTUNITYCx_IsNull && Row.OPPORTUNITYCx.Trim('"', ' ', '\t').Length > 20)
    {
        Row.OPPORTUNITYC = Row.OPPORTUNITYCx.Trim('"', ' ', '\t').Substring(0,20);
    }
    else if (!Row.OPPORTUNITYCx_IsNull && Row.OPPORTUNITYCx.Trim('"', ' ', '\t').Length < 21)
    {
        Row.OPPORTUNITYC = Row.OPPORTUNITYCx.Trim('"', ' ', '\t').Substring(0, Row.OPPORTUNITYCx.Trim('"', ' ', '\t').Length);
    }

    if (!Row.ACCOUNTIDx_IsNull && Row.ACCOUNTIDx.Trim('"', ' ', '\t').Length > 20)
    {
        Row.ACCOUNTID = Row.ACCOUNTIDx.Trim('"', ' ', '\t').Substring(0, 20);
    }
    else if (!Row.ACCOUNTIDx_IsNull && Row.ACCOUNTIDx.Trim('"', ' ', '\t').Length < 21)
    {
        Row.ACCOUNTID = Row.ACCOUNTIDx.Trim('"', ' ', '\t').Substring(0, Row.ACCOUNTIDx.Trim('"', ' ', '\t').Length);
    }
}

Answer 1

OK, so here's the bug and fix:

1. SSIS is incorrect telling you which column(s) really is/are the issue. It tells you the issue is column 16, in reality the columns where column #5 & 6 (Row.BTFAVAILABLEINTHESTATECx and Row.DESCRIBEPRICINGPARAMETERSCx)
2. the fix is: if (!Row.BTFAVAILABLEINTHESTATECx_IsNull && Row.BTFAVAILABLEINTHESTATECx.Length > 0)
   {
   byte[] copyData = Row.BTFAVAILABLEINTHESTATECx.GetBlobData(0, checked((Int32)Row.BTFAVAILABLEINTHESTATECx.Length));
   string strBlob = System.Text.Encoding.ASCII.GetString(copyData);
   Row.BTFAVAILABLEINTHESTATEC.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob));
   //Row.BTFAVAILABLEINTHESTATEC.AddBlobData(copyData, checked((Int32)Row.BTFAVAILABLEINTHESTATECx.Length));
   }

   if (!Row.DESCRIBEPRICINGPARAMETERSCx_IsNull && Row.DESCRIBEPRICINGPARAMETERSCx.Length > 0)
   {
       byte[] copyData = Row.DESCRIBEPRICINGPARAMETERSCx.GetBlobData(0, checked((Int32)Row.DESCRIBEPRICINGPARAMETERSCx.Length));
       string strBlob = System.Text.Encoding.ASCII.GetString(copyData);
       Row.BTFAVAILABLEINTHESTATEC.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob));
       //Row.DESCRIBEPRICINGPARAMETERSC.AddBlobData(copyData, checked((Int32)Row.DESCRIBEPRICINGPARAMETERSCx.Length));
   }
   

Grateful for your time and efforts :)

Answer 2

Hi @rob karatzas ,

I'm not familiar with C# code writing.

I did some online research about the issue, you may take a reference to see if it is useful.

https://stackoverflow.com/questions/48767424/ssisunicode-data-is-odd-byte-size-for-a-column-should-be-even-byte-size

https://stackoverflow.com/questions/38984053/freebcp-unicode-data-is-odd-byte-size-for-column-should-be-even-byte-size

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October