September 27, 2013

Conversion of Decimal to Comp-3

Sometimes you find something on the internet that you want to shared Norm Radder submitted some great code which I have modify and used to create a new SSIS Workflow Component that convert numeric values into comp-3. So credit to him for the below code. Also I have loss focus in the continuation of the previous post so I better start to get busy.
Cheers,



//PackedDecimal conversions 
   
  
public class PackedDecimal { 
   
   // Define constants for conversion process 
   final static int PlusSign = 0x0C;       // Plus sign 
   final static int MinusSign = 0x0D;      // Minus sign 
   final static int NoSign = 0x0F;         // Unsigned 
   final static int DropHO = 0xFF;         // AND mask to drop HO sign bits 
   final static int GetLONibble  = 0x0F;   // Get only LO digit 
   
   //--------------------------------------------------- 
   // Convert byte[] of packed decimal to long 
   
public static long parse(byte[] pdIn) throws Exception { 
long val = 0;                    // Value to return  
   
      for(int i=0; i < pdIn.length; i++) { 
         int aByte = pdIn[i] & DropHO; // Get next 2 digits & drop sign bits 
         if(i == pdIn.length - 1) {    // last digit? 
            int digit = aByte >> 4;    // First get digit 
            val = val*10 + digit; 
//            System.out.println("digit=" + digit + ", val=" + val); 
            int sign = aByte & GetLONibble;  // now get sign 
            if (sign == MinusSign) 
               val = -val; 
            else { 
               // Do we care if there is an invalid sign? 
               if(sign != PlusSign && sign != NoSign) 
                  throw new Exception("OC7"); 
            } 
         }else { 
            int digit = aByte >> 4;    // HO first    
            val = val*10 + digit; 
//            System.out.println("digit=" + digit + ", val=" + val); 
            digit = aByte & GetLONibble;      // now LO 
            val = val*10 + digit; 
//            System.out.println("digit=" + digit + ", val=" + val); 
         } 
      } // end for(i) 
return val; 
} // end parse() 
   
   //----------------------------------------------------------------------------------- 
   // Convert String of numberic characters to packedDecimal in byte[] 
   
   public static byte[] pack(String nbrs)throws Exception { 
      int digit = PlusSign;            // default if no sign 
      int startAt = 0; 
      // Test if HO char is a sign 
      if(nbrs.startsWith("+") || nbrs.startsWith("-")) { 
         digit = ( nbrs.startsWith("+") ? PlusSign : MinusSign); 
         startAt = 1; 
      } 
      byte[] chars = nbrs.getBytes();  // Move input into an array 
      byte[] packed = new byte[(nbrs.length()-startAt)/2+1]; 
      int inIdx = chars.length - 1;          // index of end   
      int outIdx = packed.length - 1; 
    
      // Now get digit to go with sign 
      int temp = (chars[inIdx--] & GetLONibble) << 4;             // Move LO digit to HO 
      digit = digit | temp; 
      packed[outIdx--] = (byte)(digit & DropHO); 
//      System.out.println("LO=" + Integer.toHexString(digit) + ", outIdx=" + outIdx);  //LO=2c OK  
   
      for(; outIdx >= 0; outIdx--) { 
         if(inIdx < 0)  break; 
         if(chars[inIdx] < '0' || chars[inIdx] > '9')   // must be between 0 & 9 
            throw new Exception("Not numberic data: " + nbrs + " at " + inIdx); 
         digit = chars[inIdx--] & GetLONibble; 
         if(inIdx >= 0) { 
            temp = (chars[inIdx--] & GetLONibble) << 4; // get digit for HO 
            digit = digit | temp; 
         } 
//         System.out.println("nxt=" + Integer.toHexString(digit) + ", outIdx=" + outIdx + ", inIdx=" + inIdx); 
         packed[outIdx] = (byte) digit; 
      } // end for(outIdx) thru digits 
   
//      System.out.println("packed=" + bytesToHex(packed) + ", len=" + packed.length); 
      return packed; 
   } // end pack() 
   
  
   //--------------------------------------------------------------- 
   // Convert bytes to hex 2-pules with blank spacer in String 
   static String bytesToHex(byte[] buf) { 
      final String HexChars = "0123456789ABCDEF"; 
      StringBuffer sb = new StringBuffer((buf.length/2)*5+3); 
      for(int i=0; i < buf.length; i++ ) { 
         byte b = buf[i]; 
         b = (byte)(b >> 4);     // Hit to bottom 
         b = (byte)(b & 0x0F);   // get HI byte 
         sb.append(HexChars.charAt(b)); 
         b = buf[i];             // refresh 
         b = (byte)(b & 0x0F);   // get LOW byte 
         sb.append(HexChars.charAt(b)); 
         if(i % 2 == 1) 
            sb.append(' '); 
      } 
      return sb.toString(); 
   } // end bytesToHex() 
   
   //------------------------------------------------------------------- 
   // Test the above 
public static void main(String[] args) throws Exception { 
/*
byte[] pd = new byte[] {0x19, 0x2C};             // 192
System.out.println(PackedDecimal.parse(pd));
      pd = new byte[] {(byte)0x98, 0x44, 0x32, 0x3D};  //-9844323
System.out.println(PackedDecimal.parse(pd));
      pd = new byte[] {(byte)0x98, 0x44, 0x32};  //invalid sign
//System.out.println(PackedDecimal.parse(pd));  
*/ 
      // Now test pack 
      String nbr1 = "-12345"; 
      byte[] packed = PackedDecimal.pack(nbr1); 
      System.out.println("packed=" + bytesToHex(packed)); 
      System.out.println(PackedDecimal.parse(packed)); //-12345 
   
} // end main() 
} // end class 

September 20, 2013

Upgrading old code part 1


I have been looking into some old legacy code that I build back in 2005, on this project we where trying to create an archive process to move data around according to specific queries and column filters.
The task is a little tedious,but perseverance and patients is the key. All the original code was wrote using VB .net and now I am translating into C# using VS 2012. Its amazing on how   this process still been used for the last 5+ years, but I guess someone has to do the update, Finally this would help me on see back on time how better or worse my coding has improved.
The first step is to transfer all the variables from one package to the other as a lazy coder I hate having to type each variable, but I could just straight into the xml code and extract the block of code from the original ssiss to insert into a brand new empty package.

 <DTS:Variables>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{017F488B-CAB0-4E2F-A28C-AA0E72EF1905}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsDropDestination">
      <DTS:VariableValue
        DTS:DataType="11">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{C57F09EB-BDAC-4044-AF7F-72F416AE7A2A}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsFastLoad">
      <DTS:VariableValue
        DTS:DataType="11">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{990E3128-0F9A-4CC9-AEE3-47C9F20CD0C9}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsRetention">
      <DTS:VariableValue
        DTS:DataType="11">-1</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{F3860C9C-9ED1-435F-BBE1-5FCF4DAE238E}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsSqlCmd">
      <DTS:VariableValue
        DTS:DataType="11">-1</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{D967607B-6FB8-4D54-B8EE-06FD3A249156}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsSqlOverride">
      <DTS:VariableValue
        DTS:DataType="11">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{9269E932-E21F-4E22-8B4B-EB6368830FE9}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="bit_IsTransferData">
      <DTS:VariableValue
        DTS:DataType="11">-1</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{7C389460-CDE6-4F0E-BB0D-F98F7C65D438}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_ArchiveExportId">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{1818DC80-D176-4F1C-9853-010A019975CE}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_ArchiveLogId">
      <DTS:VariableValue
        DTS:DataType="3">-1</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{A5171CC1-3977-43A7-AC09-1F9A1D2956D9}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_deleteStatus">
      <DTS:VariableValue
        DTS:DataType="3">7</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{B0E34C5C-7CE8-4115-BB54-F65451F6C9BC}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_DestCurrCnt">
      <DTS:VariableValue
        DTS:DataType="3">-999</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{F1014B6C-838E-45B7-8D2F-8B6D1514E51F}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_DestPrevCnt">
      <DTS:VariableValue
        DTS:DataType="3">-999</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{36698DF5-2832-4D09-B21C-5AD53CA0A925}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_FastLoadBatchSize">
      <DTS:VariableValue
        DTS:DataType="3">10000</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{8C6235C1-DB92-461D-A125-E23641704A6C}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_FastLoadCommit">
      <DTS:VariableValue
        DTS:DataType="3">50000</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{60B7481B-D459-47D1-A2B6-1F4E61946088}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_GroupId">
      <DTS:VariableValue
        DTS:DataType="3">2</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{33B5DF71-CD1C-4F54-832E-01E6BE3EF210}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_IsPkgFail">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{BD218A66-22DB-4D68-A8F1-8D5F995CBD1E}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_IsValidDestination">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{73FF4DD8-7FEF-444E-92A5-3E9E840A14A4}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_IsValidSource">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{A504BE43-075F-4177-B71B-7FE9D75ECF97}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_Retention">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{8353B03D-5BAB-44DF-A768-33272C29FD8E}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_SourceCnt">
      <DTS:VariableValue
        DTS:DataType="3">-999</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{1DD187AB-E5B4-444F-9AA7-D1D2BDC76DF3}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="int_status">
      <DTS:VariableValue
        DTS:DataType="3">0</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{A2883D87-84BB-4CBB-93A0-1D5E4E237837}"
      DTS:IncludeInDebugDump="6789"
      DTS:Namespace="User"
      DTS:ObjectName="obj_ArchiveList">
      <DTS:VariableValue
        DTS:DataSubType="ManagedSerializable"
        DTS:DataType="13">
        <SOAP-ENV:Envelope xmlns:clr="http://schemas.microsoft.com/soap/encoding/clr/1.0" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
          <SOAP-ENV:Body>
            <xsd:anyType
              id="ref-1"></xsd:anyType>
          </SOAP-ENV:Body>
        </SOAP-ENV:Envelope>
      </DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{C555DF9D-BDC4-484E-9AA9-9BDD70E70B82}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_AutoSqlCmd">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{93EDD972-9341-4D3D-B2C9-0BC8D5B7420A}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_BatchId">
      <DTS:VariableValue
        DTS:DataType="8">20111107111825</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{CAEDA2DB-311D-4C75-9EE6-2D037A3263D7}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_ComponentName">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{300F3029-A4E1-44E5-9077-30985571BE8A}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_DestinationDatabase">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{E8469E4A-79CF-4FA5-A0FF-1350DE9B84B0}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_DestinationSchema">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{5961BA4B-4E6D-4496-A3D9-5264387D8764}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_DestinationServer">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{58D49509-95CD-4C4C-BAE8-704F25AA7C7C}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_DestinationTable">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{CA481EF7-6815-4DBC-B295-5DE0C657C313}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_ExcludeColumns">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{1531B578-D9FC-4A3F-B30E-69C8C8BF740F}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_PkColumn">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{64C35DAB-9517-4301-AB13-D8811C9C819A}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_RetentionColumn">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{27E9C751-1E76-445C-AA25-0E5A9C100F1B}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SourceArchiveTable">
      <DTS:VariableValue
        DTS:DataType="8">[dbo].[tblSourceArchived]</DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{B1EA3528-0652-4022-AA0E-896165D47DC7}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SourceDatabase">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{C1DAC597-793B-4287-90F0-939128F08AC0}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SourceSchema">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{47537672-EBAD-44A3-BD2C-0988EFE1247C}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SourceServer">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{6D44E69F-94C1-4354-96C1-F4F56CABBC10}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SourceTable">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{9658F4CD-EA2D-453C-B9A8-E5B95897A599}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SqlCmd">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{FF143846-8207-461C-BBCD-4215C81CDFD7}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_SqlFilter">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
    <DTS:Variable
      DTS:CreationName=""
      DTS:DTSID="{1659F4CD-0CBB-40A3-A075-FF82C5677EF2}"
      DTS:IncludeInDebugDump="2345"
      DTS:Namespace="User"
      DTS:ObjectName="str_XmlDoc">
      <DTS:VariableValue
        DTS:DataType="8"
        xml:space="preserve"></DTS:VariableValue>
    </DTS:Variable>
  </DTS:Variables>
  <DTS:Executables>
    <DTS:Executable
      DTS:refId="Package\ArchiveData"
      DTS:CreationName="STOCK:SEQUENCE"
      DTS:Description="Sequence Container"
      DTS:DTSID="{59A61183-9C4C-4CEE-BF0D-516AFBD1005E}"
      DTS:ExecutableType="STOCK:SEQUENCE"
      DTS:LocaleID="-1"
      DTS:ObjectName="ArchiveData">
      <DTS:Variables />
      <DTS:Executables>
        <DTS:Executable
          DTS:refId="Package\ArchiveData\ArchiveTable"
          DTS:CreationName="STOCK:SEQUENCE"
          DTS:Description="Sequence Container"
          DTS:DTSID="{D4B1DA24-994A-48DE-B0F0-D4548B029B1E}"
          DTS:ExecutableType="STOCK:SEQUENCE"
          DTS:LocaleID="-1"
          DTS:ObjectName="ArchiveTable">
          <DTS:Variables />

September 11, 2013

SQLSaturday 232 Orlando Florida preparation


It is three days before SQLSaturday 232, last night I spend a good amount of time testing and adding more code to the presentation while trying to keep the presentation layout simple to follow. On Friday is the speaker dinner and instead of a normal dinner party it would be host in the organizers home which sound like a lot of fun.
It doesn't matter how many times I have done presentations, today is just one day before #SQLSaturday 232 and I do have the goose bumps, questions come into place did will I show too little or too much?
any way time will tell.

Contact Form

Name

Email *

Message *