Hi,
Looking for some guidance. We play in 3 venues but none are "home" to the Florida Orchestra. I have been trying to import the venue sales for the first time. They have not done this in the past. I am working towards streamlining this process for reporting.
I have been working with Tessitura for a few weeks troubleshooting the errors. We seem to have things mostly sorted out. I have an XML Format file as well as a txt Import order file. There is a TERMINATOR "\r\n" on the last import row.
The latest error is - Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". The import file data may be out of sync with the specified format file.
Has anyone encountered this issue and how did you resolve it?
I've researched the help guide, Troubleshoot section and a pop-up skill builder for Importing Orders. From there I looked at the T_DEFAULTS table and found 2 entries relating to OLE. See below screenshot. I'm not sure if I need to add any, one for xml and txt?
Thank you in advance!
Donna
Hi Donna,
It's been a few years since I've had to do an order import but I know what a pain they can be. Here's some info that I copy and pasted from a past support ticket. This one is from 2020, so I don't know if the activie directory issue has been resolved since then. Neither of these may be relevant to your error. There's so many things that can go wrong with these! My sympathy to you!
"Error Message: Error reading format file. Cannot bulk load because the file could not be opened. Operating system error code 5 (Access is denied)
Currently, the Tessitura Import Utilities require a user to sign on with standard login rather than with Windows Authentication. The fix for this is on the Hosting Services roadmap, but does not currently have a release date. The workaround for now is to remove the Active Directory value for your user in Security, then login with your Tessitura credentials and process the import. After this is successful, you can add the value back in to the Active Directory field in security if you wish to resume using Windows Authentication."
"What I would really recommend is using a program like Notepad++ to take a look at the values in the TW_ORDER_IMPORT table (the staging table for the imported data) for the data in question, as well as your data file to make sure there are no extra spaces or characters that might be throwing it off. Notepad++ has a feature where you can have it display all characters, and it's what I always use to troubleshoot these, and it's where I found the issue with the extra spaces in the price type and issues in the staging table."
Thank you Kanani! I appreciate your feedback.
I so want this to work I want to be able to capture the sales as well as include them in our daily report. Two of the venues are on Tessitura and one is on Ticketmaster. I feel like I am getting closer though.
Thanks,
Kanani Reichlin said most of what has plagued me (appart from some xml issues and the odd incorrect file address). Sounds like your column headers in your txt are in alignment with your xml. You can test your xml format by uploading a single data row of something you know is clean.
Notepad++ is a must (and if you have install permission issues on your computer like me at present) then you can run it off a flash drive.
Bad characters in the file are a large proportion of the errors for me in the past including things like the customer team putting semicolons and commas in the notes field, or some foreign address characters reading like new line (often German addresses for some reason). Have a look in the file making sure that their are no odd returns breaking lines of data. Katie is spot on about the extra columns. If you are in Excel and select and delete columns to the right of your last data column. And date format 100%.
I've been known to test import in batches of rows until I narrowed down the issue.
Hello Heath,
The person I am working with at Tessitura mentioned Notepad++ yesterday. She found some spaces that should not have been in the file. This makes sense. I have to take 3 versions of ticket sales data due to playing in 3 venues and manipulate them into what I need. Two of them are on Tessitura and the exports are totally different and the third is on Ticketmaster. I'm guessing that may be the issue. All my columns are in accurate. The txt and xml files match in that regard.
There is not customer data being shared so I created one constituent account for each venue for the imports.
I just worked with our IT person to download Notepad++, this should be a big help.
Thanks so much!
Notepad++ was mentioned to me way back when Tessitura Supoprt were helping me set up. It's a great resource for all sorts of things including copying and viewing data models in nicely laid our JSON from the REST API when you are writing new HTML Templates - or even looking at the HTML code you may plug into your emailer.
If you want to look for non ascii characters then this is a handy tip for finding offenders.
We regularly get/got Ticketmaster data and had them send the file in a format that we could upload automatically. It can be hard to talk them into it though (for unknown reasons). datetime or yyyy-MM-ddThh:mm:ss.nnn
Your XML file will have TERMINATOR="\t" no doubt for tab delimited fields in the middle of a row and "\r\n" for the line end. Anything sneaking in as a "\r\n" (return) or and additional "\t" will mess your file format up. You can add returns in the notes field in Tessitura so possibly some externals as well.