Saving Tess files w RAMP

Had this conversation with a user again yesterday.  It really is a nuisance, and I don't particularly like the idea that we'd need to add new folders to any local machine that might run Tessitura.  I think I'd rather have the option for each user to have access to their own RAMP desktop (or a subfolder of it) via FileSaver/FileMover.  I'm going to ask RAMP about that, but it seems like such an obvious idea that there must be some reason they haven't done it.

There used to be a Desktop access option (because I had access to it) via 2X back when RAMP was connecting that way, but it went away when Citrix came around.  I would not mind seeing that come back again.

I am not sure what the solution is, but there definitely should be a simpler way of doing this.  I am not sure how related this is to it, but to me it seems like it might be.  We have found that our users are regularly asked by their Windows application to "permit access" to the Tessitura (and related) applications every time they log on, even after clicking "remember my choice" when that option is present.

John A. Moskal II said:

There used to be a Desktop access option (because I had access to it) via 2X back when RAMP was connecting that way, but it went away when Citrix came around.  I would not mind seeing that come back again.

Yeah, that went away with 2x, which sucked because I did a lot with that.

However, that Desktop (the Windows desktop of your RAMP account) is exactly where your file save dialog box begins.  And (just checked) you can drill down from there to the IMPFTP folder where Extraction files are dumped.  That part is only slightly less annoying than trying to find your own desktop on our local machine, but if an link on everyone's RAMP Desktop to that folder would be a help.  However, for security I'd definitely prefer that each user see an individual location in their FileSaver.

I have to believe that Version 15's Windows integration for logins HAS to make something like this a lot easier, or at the least, make making that enhancement to Tessitura a lot more programmable than it is now.  It seems counter-intuitive that they would be able to integrate login credentials and not able to do something as basic as identify a default save location.

Then again, it has been a while since I did direct application programming as I have mostly stuck to database and website work over the last 5+ years.

Maybe.  Counter-intuitively when we moved to Windows Auth for SSMS I lost the ability to do BCP on RAMP because of an inability to match file system permissions that had been available to SQL Server authenticated accounts.

Did you lose the ability to run them, or could you run them but it would just not save correctly?  Because with the upgrade to Version 14, you no longer have full sysadmin rights (assuming you had them in the past).  You still have a fair bit, but they are no longer giving full on sysadmin rights anymore.

It's ultimately a file access thing, and it's much old than the v14 upgrade, it started when they started offering Windows Auth for SSMS (and the Security Tool).  For a while the let me keep a SQL Server Authenticated account, but after the v14 upgrade they took that away from me.  We've spent a year trying different things, but RAMP has been completely unable to configure an account for me that does Windows Auth and has file access.

I'm working on a project now to change my BCP utility into a SQL Server Agent job.  That way I can give it to them, they can give it to SA, and then (as a SQL Auth account) it will have the permissions it needs to run BCP.

To be specific: I run BCP to import NCOA data into impresario for our custom NCOA process.

We also have a custom NCOA process, but I just wrote a pretty simple process to follow for it.  I have always just used the fun and pretty basic "Import/Export Wizard" to bring the stuff to a local table that I create every time with the data coming back from the NCOA people in the form of an Excel file (though when I am trying to locate the Excel file on our shared drive, I do get to go through the fun search and find process through all the folders to locate the document that was the start of this whole thread in the first place).

Once that data is loaded though, I have three different, quick procedures I run to verify the validity of the return data, compatibility with customer accounts in the database (excising any changes between the time the data was sent to NCOA and then returned) and then finally to update the data to the customer records.  I then drop that local table so that it can be created again next time through the utility and referenced by the procedures again without having to change anything.

It does not take that long at all and has worked pretty well for us.  We also only do NCOA like every two or three months.

Ours is a bit more involved.  We get back a formatted file from our NCOA vendor, then I run it through a python script to strip out uninteresting columns and unchanged addresses, then I (used to) place it on the RAMP servers and used BCP to pull it into first a staging table, but later changed that to a temp table.  I still have a secondary staging table where I keep a historic record of imports and also make sure that type conversions have worked.

We use a custom process for three reasons:

1) We avoid making changes, as you mentioned above, to addresses updated between the NCOA export and the attempted import.  Also we check to make sure that we aren't updating any accounts that have been merged in that time.

2) We don't want to delete any addresses.  Instead we inactivate them and add a special Contact Point Purpose that explains why they were inactived.

3) We don't want to automatically update VIP customer accounts.  Often our preferred addresses for communicating with them are not their Post Office official address, so we need to be able to review them before any changes are made.  Tessitura's utility allows you to screen users based on a list, but it won't then let you know that they were recommended for a NCOA update or what the recommendation was.

Sounds fun.  We do not delete any addresses either but also inactivate them.  Instead of a contact point purpose, though, we have a specific NCOA address type we use for those.

Your #3 is not really a big issue for us, which helps, and usually our Development department is A) already on top of them before the NCOA is sent and/or already alerts me to the names which might be issues and I just remove them from the data.

We also do not store a historic record of imports in the database.  I have thought about it but decided that it is not that big of a deal to me as I still have all the initial files (because I save everything), and no one else at my organization would care enough.

John A. Moskal II said:

Sounds fun.  We do not delete any addresses either but also inactivate them.  Instead of a contact point purpose, though, we have a specific NCOA address type we use for those.

I did that initially, but later decided I wanted preserve the original address type.

John A. Moskal II said:

We also do not store a historic record of imports in the database.  I have thought about it but decided that it is not that big of a deal to me as I still have all the initial files (because I save everything), and no one else at my organization would care enough.

I make a point of deleting old files since they're full of PII, but it's probably stupid of me to save the old staging info.

I know too many other staff at the organization are saving that type of personal information to the shared drive, otherwise I would worry about that, too.  Like you, I saved them as a "just in case" measure, but I have never once needed them thus far.

As for storing personal information on the shared drive, that is one of the many things on my list in terms of improving for the organization, but I am still working on senior staff buy in on a few things.  Assuming I get there, I know exactly where I have saved it all, but I am sure there is going to have to be a massive search through everyone else's files anyway.

I know too many other staff at the organization are saving that type of personal information to the shared drive, otherwise I would worry about that, too.  Like you, I saved them as a "just in case" measure, but I have never once needed them thus far.

As for storing personal information on the shared drive, that is one of the many things on my list in terms of improving for the organization, but I am still working on senior staff buy in on a few things.  Assuming I get there, I know exactly where I have saved it all, but I am sure there is going to have to be a massive search through everyone else's files anyway.