SQL Bulk Copy with duplicate keys

Given:
- Large xml file containing entries that need to be in my database table. Some rows have same value for the table key (not necessarily completely duplicate rows!)
- SQL table, C# code

Problem:
Copy the entries from xml to SQL table. This is done fast using SQLBulkCopy function in .NET, but does not tolerate when input data has duplicate values for the primary key column. I really do not care about keeping the rows that are duplicate. Just throw them out so I can use the bulk copy for the 99% of the data.

Solutions.
Online blogs suggest a number of solutions:

- One is to create a temporary table with no duplicate entry constraints, bulk copy everything into that and then use SQL to copy temp table into the final table while maintaining constraints.

- Another way is to find all duplicate rows in a table in code, using hashtables.

- Next, using the Table.DefaultView.ToTable(distinct=true, columns), but this only removes duplicate rows, not  rows that have a certain column with duplicates.

- There is a way to do that with LINQ but I didn't even get to read that.

-------

I used a pretty short (imo) and fast way to do it using IEqualityComparer interface:

class RowEqualityComparer : IEqualityComparer

{

    public bool Equals(DataRow b1, DataRow b2)

    {

        return ((string)b1["Symbol"]) == ((string)b2["Symbol"]);

    }

    public int GetHashCode(DataRow b1)

    {

        // I'm not really sure what to do here or whether I should even worry

        return b1["Symbol"].GetHashCode();

    }

}

and then simply 

BulkCopy.WriteToServer(
           dst.Tables[table].AsEnumerable().Distinct(

               new RowEqualityComparer()).CopyToDataTable());

instead of 

sbc.WriteToServer(dst.Tables[table]);

FTP with MS Access

Given: Access linked database, FTP server and account, VBA code that creates/reads some data files

Problem: Send a file to server and read files from server in a reliable manner, without making complications with eventual installation and usage of the program.

-------------------------------------------------------
There appears to be 2 ways of using FTP with MS Access. One is to use a control object (http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm) which has to be installed and registered on the user machines. Having made that mistake with the Fedex program, I try to avoid that process.

Second way is to use blocking shell function (http://bytes.com/topic/access/insights/841878-shellwait-function) and call cmd.exe ftp (http://miketurco.com/ms-access-ftp-using-vba-102199). Using command prompt output redirection, my program can even try to understand what is happening. This works neatly and sends/reads files with little code. It is certainly not without problems though!

Main issue is there is so much stuff that can go wrong, and the command prompt will simply end, with no warning. That's not reliable. Ideally, I'd like to have error codes returned... such as "Invalid login info", "No internet connection", "Server does not respond", "File already exists", etc. Instead, I have to parse the redirected output and deduce what happened. What a mess, this is going to be hard to debug and maintain in a month.

Second issue is that this process, though simple, seems to take a lot of time to run (I have to use blocking Shell command to avoid file errors). I bet the actual ftp transfers take less than half the time. This becomes a problem when my program needs to check the server periodically for response files - it is not good when every 10 minutes my code makes everything else slow for 5 seconds while checking the server. I suppose I am forced to make the user manually check for updates by clicking "Refresh" button.

Well, this is VBA, did I expect it to be anything but a mess?

LightningSource Drop Ship API

Trying to understand the Lightning Source EDI files, and create my own EDI request files.

Registration-free COM objects

Problem:

A new problem has came up regarding the Fedex shipping integration. I have made my solution depend on a COM DLL file, that has to be registered by administrator on any computer that runs the Access file VBA macro. That means, for multiple users the administrator has to install this as an update (copy DLL files, make sure user has NET framework 4 installed, run registration script, update Access file). The old version of the software is actually no better - it requires the Fedex Ship Manager API to be installed on user's computer, presumably by an admin too. But, we want to make it easier and skip the admin installation part altogether - just put the program files on the server, let user copy them to local computer and use with no installation by admin.

This would in fact be possible if I had succeeded in making VBA call webservice directly, using VBA import web service tool. But, I'd rather not go back to it because working with variants is very painful.

What I want to investigate are the registration-free COM objects. Also, somehow the old code uses DLL libraries with no reference to them in the VB Editor References window, by simply Declare and CreateObject calls. Finally, I've been using wsdl files, but Fedex also alternatively provides XML (.xsd) files. 

Accessing Fedex web services with Access VBA code - project overview

Problem:
Fedex has changed their API so I need to change some Access VBA code to interface with their new web service API.

Given: 
Linked Access 2003 database with lots of tables and forms.

- Some of the forms use code that interfaces the Fedex shipping services - place order, track shipment, cancel, close shipment, request signature proof. The old Fedex API apparently requires a long request string with a bunch of numeric codes that seem to be a hassle to program, maintain and debug.

Fedex WSDL files

- Ship, Track, Close, etc

Fedex sample projects to access the web services. These are available in  a bunch of languages, from VB.NET to PHP. Documentation is available also, as well as a technical help number to call.

- For development, Fedex provides a special testing server and requires you to register to access the web services (https://wsbeta.fedex.com:443/web-services/...)

What I did and tried:

I registered as a Fedex developer and got key/pwd/account/meter numbers, and inserted them into corresponding fields in the sample projects to make them work.

The web services I have used before were given as a link that you could navigate to in Visual Studio with "Add Web Reference". The .wsdl files still work the same, except I just need to paste the path of wsdl file into add web reference address box.

Access 2003 VB editor does not have "Add Web Service Reference" by default. It is possible to install an add-in that will let you do that. However, what it did for me was create a bunch of classes (in VB 6!!) defined by the wsdl, and several wrapper classes too. It looks like it would work nicely for small and simple projects... But VBA is a pretty old language that I do not care to be an expert in. The generated classes used Variants for a lot of "complex types" that VB 6 did not have the power to create. It was a messy solution and eventually I ran into problems with Variants that I could not figure out. In retrospect, even if I managed to make shipment request part work, I would have ran into even more problems if I tried to add another web reference due to Fedex sometimes using same classes in their wsdl's.

So, I decided to modify the given sample code into a dynamic library with a simple interface that VB 6 could easily access. I made a VB.NET library project, added a COM class and used the sample code to define that class. In the project settings, I enabled the "COM Interop" option. I also added the wsdl reference. This compiles as a COM Dll file.

In Access VB editor, I added a reference to the newly created FedexShipAPI.tlb and updated the code to use this instead of the old API. It works nicely and returns a shipping label.

When I tried to add other Fedex web services to same project (it would be nice to have all Fedex functions in one library, right?) I ran into problems. Apparently the different wsdl files use a lot of same classes and Visual Studio uses namespaces to differentiate them. So far ok, I just needed to sometimes specify the full namespace names and I made a DLL with Ship and Track services. However, in VBA this created a mess because after importing the new DLL reference, most enumerations suddenly went crazy from 'FedexAPI.ServiceType_FEDEX_GROUND' to 'FedexAPI.FedexAPI_FedexShipWebReference_ServiceType_FEDEX_GROUND' and 'FedexAPI.FedexAPI_FedexTrackWebReference_ServiceType_FEDEX_GROUND', and even longer than that. To complicate things, some of them stayed the same since they were not duplicated in original VB.NET project. Although this would probably eventually work, I decided against making single DLL and made a separate DLL for each service. That worked fine.

Problems and TO DOs

International document shipment: there is no sample to do this :( So when I try to modify international commodity shipment - specify package contents to be DOCUMENT_ONLY and do not add any commodity descriptions, the server still returns an error message that says international shipments must have at least one commodity specified.

International Ground shipment (only should work to Canada/Mexico) -- there is no sample code for this too. Seems pretty easy to do though, right? I just changed the recipient address to a valid address in Canada, and set the service type to International_Ground. The server always returns "Invalid service type" error, however. 

Calling Fedex help desk on this was no help. The guy suggested I use some numeric values (92) for international ground, which I was supposed to plug in into the XML request file. Looking at the provided documentation, there is NOTHING that makes any reference to using numerical codes to the service or other enumerated types, even when building the xml request files manually. In fact, this seems to be something from the old API, so I think the help guy was incompetent. To the my question why they provide the sample codes at all, he replied that the code is useless, he was not the one to make it, and that I should work with xml.

CustomerReference: I can not add more than 3 customer references! The server returns a schema error if I provide 4 different types of reference (invoice, PO number, dept No, reference No), but no error when I provide any 3 of those. This is not important for me though.

I am having trouble registering the DLL files on the user computer. Forums suggest using RegAsm.exe (I have to use version 4.0.0.something since I made the DLL using .NET 4), something like 

> regasm FedexShipAPI.dll /tlb:FedexShipAPI.tlb

but I get Object reference errors when trying to use the library in Access. I will try signing my projects (Project settings -> Signing) and then using /codebase switch:

> regasm FedexShipAPI.dll /tlb:FedexShipAPI.tlb /codebase

I still need to request label certification from Fedex. I don't know whether I should fix the two problems with international shipment first or hope their test cases might clear this for me. The rest of the shipment kinds seem to work fine.