We're hosting an application on Appian cloud with VPN SQL server instance to

Mike over 11 years ago

We're hosting an application on Appian cloud with VPN SQL server instance to our servers. The end-user of the application is our customer and they are required to provide SQL data views to application processes on a periodic basis. Their SQL views are not exposed via VPN and are not available to us through their firewall, so they need to deliver a back-up or snapshot of data that we then bring into process somehow. We're looking for ideas to read this data when it is not available for direct access. A thought is to have them upload a view or even a csv thru a task form into the knowledge center and then have process read and save the data into a data store. Any good ideas out there? Thanks!...

Discussion posts and replies are publicly visible

0 Sathya Srinivasan
Appian Employee
over 11 years ago

In similar line to CSV, is it possible for you to consume the CSV and update your SQL server database (creating temp materialized table, for eg). They can use Appian to upload the document and you can probably write a process to consume and update your DB all through process. The advantage of this approach is that you can work with a higher degree of flexibility in the process if your data is from a DB table rather than having to read the CSV file every time. It also allows you to write extended views etc., to slice and dice the data in the way you want which can be a bit tricky from a CSV.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Mike over 11 years ago

It'd be nice to consume a SQL view or table from the client, but I don't know how Appian can allow us to upload a view into the KC which a process can then read or load into our local database that supports the application. I agree with your suggestion, but curious if there's any OOB features to work SQL artifacts rather than csv's? thanks
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 11 years ago

There are plug-ins that'd parse CSV if they are in a defined format - that's for a start. As long as the inputs are in a defined format, you should be able to build a process around it and once you have the data, the rest is straight-sailing. Look at Parse CSV plug-in available under shared-components.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Mike over 11 years ago

great, we've used this plug-in before. we just need to ask the client to provide data in csv format which should not be a problem.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Sathya Srinivasan
Appian Employee
over 11 years ago

Sql Server has native capabilities to export data in CSV format. You can capitalize on this functionality and ask the client to follow a standard routine. Since this is machine generated document, the possibility of the format changing will be less (unless the underlying schema changes)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel