Using any ODBC data source and select statement, you can provide the data records to the transformation templates.
you can use an SQL statement to get the data for the records passed to it.
The dialog window looks like this.
Connect String
The connect string at the top tells the application how to get to the data records
Examples of connect strings are :
For Microsoft Office Excel file with a simple structure
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\MyExcel.xlsx;
For MS SQL database :
Driver={SQL Server Native Client 11.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
As a useful resource, the web site www.connectionstrings.com goes into a significant amount of details into ODBC connection strings and variations.
SQL Statement
The larger panel at the top is for an SQL statement to retrieve the data records.
As an example, this is a typical select from MS Excel where column names are available in the sheet or view
SELECT * FROM [Sheet1$]
or from an SQL database table called Persons
SELECT Firstname, Surname, Address1, Address2, Address3, Address4, Postcode FROM Persons
This is dependent on the record data you need and the structure of the data your data source uses.
The panel at the bottom is to show the field names and example data from the first records. These are used for the markers in the output templates and other parts of the process.
Designing Data source dialog
To configure where the data is, a database connection can use the ODBC dialog to set how to connect to the data in an ODBC connection by clicking on the Design Connection button.
The ODBC data connection dialog will look like this
For the data source type, you can use the computer’s 64 bit ODBC types. A list is shown in the drop-down that is acquired from your local machine.
The list is the same as the 64 bit ODBC data source types that your computer uses.
This can be found by locating ODBC Data Sources 64 bit on your computer menu or settings. The list of drivers will be seen when you are creating a new data source in that system dialog.
Once you have selected the driver, enter the server source and details, or the DBQ file if the driver is for Excel or Access.
Once the ODBC driver is selected and the details populated, the ODBC dialog box may look something like this
If you enter details into the ODBC connect string, it will populate the other details as appropriate.
Once this is done and OKed, an SQL statement will allow the selection of data from the source in the previous dialog for data source.