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

rob karatzas 101 Reputation points
2021-06-28T22:30:34.447+00:00

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);
    }
}
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,453 questions
0 comments
Accepted answer
  1. rob karatzas 101 Reputation points
    2021-06-29T13:17:09.383+00:00

    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 :)

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 32,821 Reputation points
    2021-06-29T06:06:59.203+00:00

    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

    0 comments