QuerySurge Technical Whitepaper No. 10
QuerySurge can access Flat Files stored on a SharePoint server through a custom Connection using QuerySurge's Connection Extensibility feature. The following setup is for an "On Premises" SharePoint server (and not, for example, for a SharePoint instance in the public cloud, which has different authentication needs).
For this setup, there are three major steps that need to be completed in order to set up the Flat File Connection for SharePoint files.
- Create a Schema file
- Configure the Agent's login account
- Create a Custom Connection using the SharePoint URL
Schema XML File
As with all custom Flat File setups, a custom schema.xml file is required. The schema file specifies (among many options) the "table" and "column" properties of your CSV/text files when processed by QuerySurge. You can see the basic implementation of a custom schema file here. You'll need to create a custom schema.xml file for each file layout that is on SharePoint.
Set up the Agent Login
The QuerySurge Agent installs as a service on Windows. The default user account on Windows under which services install is the "Local System" account, a special Windows account for services to run under. It is possible that the "Local System" account will not have access to the SharePoint Server. For this situation, you can change your Agent service to run under a login account of your own choosing. The account specified must be granted access to the SharePoint server's directory containing the Flat Files and schema files.
Additional information on how to configure the login for an Agent can be found in Setting an Agent to Run Under a Specific Login Account on Windows. In the image below, setup of an Agent service login account that also has SharePoint access rights is illustrated, where "<SharePointUser>" refers to a domain user that has rights to access SharePoint.
Creating a Custom Connection for Flat Files on SharePoint
When creating your custom Connection, you will not utilize the built-in Flat File connection type in the Connection Wizard. Instead, use the Connection Wizard's Connection Extensibility feature. A general description of the Connection Extensibility feature is available here.
This part of the setup starts with the Data Source drop-down in the Connection Wizard. Select the "All Other JDBC Connections (Connection Extensibility)" option from the drop-down menu.
For your Driver Class, enter jstels.jdbc.csv.CsvDriver2.
Note: If you did not deploy the Flat File drivers during your Agent installation, you'll need to add them before continuing. You can do this with the QuerySurge installer. Make sure to use the same version installer as the version of your deployed QuerySurge.
Next, you'll need to enter your Connection URL. The URL requires the paths to schema and data files. There are two ways to set up the custom schema file. One way is to post the schema file to your SharePoint server. The other is for the schema file to "live" in a directory local to the QuerySurge Agent(s).
Setup with the schema.xml on SharePoint
If your custom schema.xml is on SharePoint, a basic connection URL template (for a pipe-delimited data file with a ".txt") extension is:
jdbc:jstels:csv:<SharePointURL_PathToFlatFileDirectory>?dbInMemory=false&commentLine=--&separator=|&suppressHeaders=false&fileExtension=.txt&schema=<SharePointURL_PathToSchemaFile>&dateFormat=yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss | yyyy-MM-dd | HH:mm:ss.SSS | HH:mm:ss&escapeEOLInQuotes=false
To use this URL, the <SharePointURL_PathToFlatFileDirectory> and <SharePointURL_PathToSchemaFile> need to be replaced by the actual SharePoint URLs to the Flat File SharePoint directory and to the schema file SharePoint directory.
Note: <SharePointURL_PathToFlatFileDirectory> is a URL to a SharePoint directory; it does not include your target Flat File name. <SharePointURL_PathToSchemaFile> is the full path to the schema file on SharePoint, including the schema file name. (These rules apply whether the files are located in a local directory location or on SharePoint.)
Note: The Agent login account used must have access in SharePoint to both of these files.
Note: SharePoint URLs may require encoding as shown in the example, where we use "http://SharePointServer/Shared%20Documents/" in the place of the more conventional "http://SharePointServer/Shared Documents/", where "%20" (the encoded value for the space character) replaces the space character in the URL.
In the example above, the Flat File and the schema file both reside in the "Shared Documents" directory on a SharePoint server named "SharePointServer." Depending on your SharePoint server's set up and folder structure you will need to modify these attributes of the URL.
Setup with the schema.xml in an Agent-local directory
If your custom schema.xml is stored locally on the Agent machine (i.e. it is not in SharePoint), the schema attribute should contain the full local path to the schema file. A basic connection URL template (for a pipe-delimited data file with a ".txt") extension is:
jdbc:jstels:csv:<SharePointURL_PathToFlatFileDirectory>?dbInMemory=false&commentLine=--&separator=|&suppressHeaders=false&fileExtension=.txt&schema=<Local_PathToSchemaFile>&dateFormat=yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss | yyyy-MM-dd | HH:mm:ss.SSS | HH:mm:ss&escapeEOLInQuotes=false
To use this URL, the <SharePointURL_PathToFlatFileDirectory> and <Local_PathToSchemaFile> need to be replaced by the actual SharePoint URL to the Flat File SharePoint directory and to the full local path of the schema file.
Note: <SharePointURL_PathToFlatFileDirectory> is a URL to a SharePoint directory; it does not include your target Flat File name. <Local_PathToSchemaFile> is the full path to the schema file, including the schema file name. (These rules apply whether the files are located in a local directory location or on SharePoint.)
Note: The Agent login account used must have access in SharePoint and the local file directory.
In the example above, the "MySchemaFile.xml" file resides on the Agent machine in the "C:\MyFile" directory.
QueryPair Syntax
Once your Flat File, the corresponding schema.xml file and the Connection have been set up, you are ready to write a QueryPair. The SQL statement should reference the file name of your data-containing Flat File as the table name. If the file extension has been defined in your schema or Connection URL then you can simply use the file name as the table name; otherwise, you should include the file extension. Table names may be enclosed with double quotes; table names with any non-alphanumeric characters (i.e. special characters, including the period before a file extension) must be enclosed in double quotes.
With this setup, Flat Files on SharePoint can be a rich data source for QuerySurge!