Rob Symonds

How to Convert Taxware Tax Exempt Certificates to Vertex Tax Exempt Certificates

By

As part of a project at work last year I had to convert ADP Taxware tax exempt certificates to Vertex tax exempt certificates. I searched the web and didn’t find any information on how to go about doing this. I have to believe that somebody has tackled this situation before and applied a much more elegant solution. But sometimes you just need to get something done regardless of how “pretty” the solution is.

I considered a few different approaches for tackling the problem. I’m not 100% happy with the final solution but it got the job done.

Defining a Starting Point and Ending Point

Taxware provides a reporting feature with their STEP application but this report is not CSV, tab-delimited or fixed-width format. It is not really usable as an export. First, because the format is not easy to work with and second because it doesn’t contain all of the information for the certificates. However, our Finance folks said the reports had enough information to use for the exemptions. So the issue that remained was how to get the Taxware report into a format that I could work with.

Vertex Q Series provides a useful Import/Export tool that allows fixed-width data files to be imported straight into the data repository (in this case SQL Server).

In a nutshell then, the objective was to take the Taxware exemption certificate report and convert it into a Vertex import file.

Possible Approaches

The first thing I thought of was writing a C# console application to handle the whole conversion. This is a bad habit of mine. I’m sure you’ve heard the phrase about “he who has a hammer treats everything like a nail”. I spent much of my career as a programmer so this is always the thing that first occurs to me for better or worse.

I also thought about PowerShell but I find PowerShell difficult to work with. I always find it doing things I don’t expect it to do. This isn’t because PowerShell is bad. It’s just because I bring too much baggage from other languages to the table and haven’t had enough time to work through it. Time was critical on this project so PowerShell was out.

I also considered using SSIS. SSIS is great for doing things that Microsoft has already thought of and designed components for. I’ve found though that when I need to do something that falls outside that sweet spot, things can get complex quickly. At that point, I was still new to SSIS and didn’t want to build my own components so SSIS was out too.

After looking through the Vertex documentation on the import file I realized I would have to pull in some customer data from our PeopleSoft Financials system and would also need to write some functionality to pull off Vertex Geocode tables to get my data ready for import. This type of thing is easily done with straight T-SQL using scripts.

The file to be imported into Vertex would need to be fixed-width format. That is easily done through SSIS.

Final Strategy

The final strategy I decided upon was:

  1. Parse the Taxware exemption certificates and convert the data into a tab-separated file using a C# console application.
  2. Load the Taxware data into SQL Server staging tables and scrub/prepare it using T-SQL.
  3. Export the data to a fixed-width flat file from SQL Server using an SSIS package.
  4. Load the data file into Vertex using Vertex’s Import/Export utility.

Looking back, if I had a bit more time I could’ve compressed steps 2,3 into one step using just SSIS. Like I said above though, sometimes you just need to get the job done even if it’s not very pretty.

If anybody is trying to tackle this problem, email me and I can send you some of the code I used to parse the Taxware report. It’s not perfect but it could save you a lot of time.