Data Connections
How to Connect a Data Connection in Manage
- In Manage, go to Data Connections and click the New Data Connection button.
- Enter the ‘Data Connection Name’ in the textbox given, select the ‘Connection Type’ from the drop-down list and enter the ‘Connection String’ in the Connection Attributes field for the specified ‘Connection Type’. For more information, refer to Data Source Connection Types:
- Click the Test Connection button to check whether the connection has been established.
- Click Save.
Data Source Connection Types
SmartIQ uses connection strings to establish a connection to an external data system. There are several data sources from which the data can be can be read and used by SmartIQ – CSV, OLEDB, Oracle, Web Service, XML, SmartIQ
CSV
The CSV connection type enable the connection to a CSV (.csv) or Text (.txt) file. The format for the connection string is:
csv = data-source-path; fields = field-name1, field-name2,….; hdr = header-row-number; delimiter = delimiter-character; skipheaderrows = number; skipfooterrows= number; widths = width-of-field-1, width-of-field-2,….;
Connection String Parameters | Description |
---|---|
csv | Path to the text or csv file |
fields | Names of the fields in the order they appear in the file. |
hdr | Specifies the row which is to be considered as the header to retrieve the names of each columns |
widths | Sets column widths if the text file data source has a fixed width format. Use -1 for a variable width data source |
delimiter | Set a custom field delimiter. When this value is not supplied the default comma delimiter will be used. |
skipheaderrows | If the text file contains header rows that need to be skipped. State the number of rows to ignore from the top of the CSV file. |
skipfooterrows | If the text file contains footer rows that need to be skipped. |
The parameters .csv and fields/hdr are mandatory and the remaining are optional in a .csv connection string.
Example Connection Strings
csv=c:\temp\book1.csv;fields=Name,CategoryId;hdr=1;delimiter=|
csv=c:\temp\book1.txt;fields=Name,CategoryId;widths=10,5
csv=c:\temp\book1.csv;fields=Name,CategoryId;skipheaderrows=2;skipfooterrows=2
Example CSV with header and footer rows
CLIENT REPORT 01-01-2012
ID,fName,surnam
01,Ben,Smit
03,Sally,Jon
END OF FILE
In recent versions, if the hdr=1 (e.g. first row is header row) attribute is part of the connection string, the field names will be picked up from the txt file and the 'fields' attribute can be left out.
SQL Server
The SQL Server Database Action Delivers Data to SQL Server via a Stored Procedure via OLEDB. This connection type will produce the best performance for SQL datasources compared to other connection types.
The basic format for the connection string is:
Server=server-name;Persist Security Info=True/False;User ID=user-ID;Initial Catalog=SQL-Database-name;password=login-password
Connection String Parameters | Description |
---|---|
Server | Specifies the server/database name |
Persist Security Info | It is recommended to set this to False when the user ID and password are included in the connection string |
User ID | Login ID |
Password | Login Password |
Initial Catalog | The SQL database name |
Example Connection Strings
Server=tcp:xyzsqlserver.database.windows.net,1433;Persist Security Info=False;User ID=infinitiID;Initial Catalog=Sample Data;Data Source=intelledoxXYZ;password=infinitipassword
SQL Server connections usually require a username and password, these credentials can be part of the connection string or more securely, the username and password fields can be used on the data source screen properties hiding the password from plain view.
Web Service
The Web Service connection type enables the connection to web services which allows users to access the attributes of a function.
The format for the connection string is:
http://webservice link
Example Connection Strings
http://intelledoxXYZ/sampledata/service1.asmx
XML
The ODBC connection type enables the connection to an XML data source. The format for the connection string is:
xsd=xsd-file-path; xml=xml-file-path;
Connection String Parameters | Description |
---|---|
xsd | Path to the xsd file |
xml | Path to the xml file |
Example Connection Strings
xsd=C:\temp\Books.xsd; xml=C:\temp\Books.xml;
SmartIQ
The SmartIQ connection type enables the connection to the internal SmartIQ data source that the instance of SmartIQ is currently using. A connection string is not required for this data source type as this is already known.
JSON
SmartIQ supports the JSON file format for data connection.
JSON Data Connection
The connection string format has a "schema" and a "json" part to it. "schema" points to the schema file in draft v4 syntax and "json" points to your data file. For example, "schema=c:\temp\products.schema.json;json=c:\temp\products.json"
Data object Name is not used and any value can be entered.
Display fields start with a "$" which just indicates the root of the data, "[]" indicates that this item is repeated.
Filter fields are any path that is not the repeat path itself (ends with "[]").
JSON data sources only support the hierarchical setup for nested repeating items.
SmartIQ supports JSON schema draft v4.
REST
For more information, refer to Rest Data Connector.
OLEDB
Note
For performance, security and forward compatibility reasons, the OLEDB connection type should only be used if it is the only connection type that can be used for a data source. SQL Server connection type should be preferred whenever possible.
The OLEDB connection type enables the connection to which only support web authentication data sources such as SQL server data sources. The format for the connection string is:
Provider=provider-name;Persist Security Info=True/False;User ID=user-ID;Initial Catalog=SQL-Database-name;Data Source=server-name;password=login-password
Connection String Parameters | Description |
---|---|
Provider | Specifies the Database provider |
Security Info | The security information. |
User ID | Login ID |
Password | Login Password |
Data Source | The server name |
Initial Catalog | The SQL database name |
Example Connection Strings
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=infinitiID;Initial Catalog=Sample Data;Data Source=intelledoxXYZ;password=infinitipassword
OLEDB connections usually require a username and password, these credentials can be part of the connection string or more securely, the username and password fields can be used on the data source screen properties hiding the password from plain view.
Updated almost 2 years ago