I am looking to create a custom utility that would import data from a txt file and insert into a custom local table (LT). I'm finding a lot of the procedures and functions used by the existing import utilities are not accessible to regular users or possibly they aren't accessible when logged in using windows authentication, since I know you can't use that when running imports in Tessitura.
So far with the help of Tessitura, I've gotten as far as running the AP_BCP_FILE procedure. The txt file has 2 columns and 3 data rows with a header row. I attempted to run it with a '-c' in the format file parameter but it kept telling me there was an invalid end of file. I then decided to create a small xml format file where I could specify the end row characters \r\n. But now I'm getting a different error: Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification. The format file data type specifications should match what is listed in the format file so not sure where to go from here. So if anyone has ever done anything like this, I'll appreciate some insight.
And just some clarification why we don't just use the SQL Import Tool. I can use that tool and have used it many times, but I'm hoping to empower other Tessitura users the ability to upload data in the future without my help or without me needing to teach them how to use the import tool.
Hi Jesse,
Have a look at [dbo].[IRP_DONORSEARCH_BULK_IMPORT] and see if that provides a model from which you can work.