First off, some background. I have been using and troubleshooting Data Integrator related issues for several years. It's a good tool, and quite handy for importing and updating data in the IBM TRIRIGA application. That being said, Support has received quite a bit of traffic with regard to using this tool and so I thought I would provide my take and open a dialogue for commentary.
The most pervasive issue I have seen overall has been problems with the source sheet. Typically, I start my Data Integrator process by building a spreadsheet with the column headers using the TRIRIGA application. Starting from the Data Integrator interface, reached from Home > Tools > Data Integrator interface using the Create Header action you can generate a base sheet. It's as easy as selecting the fields you want to use and exporting a sheet to begin working with. You also have the option to simply open Excel and type in the fields you want to use.
A known issue is encountered when copying and pasting from the application into Excel. In fact, this is one of the key points I want to make. Copying data from the TRIRIGA application or from any other tool, into Excel, will almost invariably introduce formatting into the spreadsheet. This is the most common cause of issues with the upload process. HTML formatting information will cause problems with the upload.
The method I have found that aids in getting around this restriction is to use the Copy/Paste VALUES option when pasting data into the spreadsheet. This removes the formatting tags, and allows for a clean upload. At times, I have experimented with copying and pasting an entire spreadsheet into a new sheet, again using the VALUES option, to clean up an upload sheet prior to saving as a text file. This yields good results and has solved many issues for me.
Another area where I have encountered issues in the past is when trying to make edits to the text file after exporting it from Excel. I strongly recommend that this NOT be done. If any edits are warranted, please make the changes in the spreadsheet and re-export the text file. In fact, I would recommend deleting the original text file and doing a fresh export each time any edits are made. This eliminates the possibility of bringing in bad data, or merging unexpected edits.
Source data can introduce issues as well. Flat file outputs from third-party software may not contain all of the data need for either upload or update. Missing column data can cause missing rows or mismatched data post-upload. Also, checking and verifying the input data can be tedious. If there is any doubt about the source data, I would recommend using one of the other integration methods where you can engage workflow to trap erroneous or missing data.
Because Data Integrator uses a flat file transfer methodology, there is very little in the way of error trapping involved, and there are no real trigger points for validation workflows to check the data prior to saving the data. If your source data is in doubt in any way, I have to recommend using Business Connect or Data Connect to bring your data in.
I welcome any comments or suggestions for extending this post, and Happy Integrating!