Using an OleDB data source and select, 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
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
For MS SQL database using your user id :
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
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 you may do something like this
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 OleDB dialog to set how to connect to the data in an ODBC connection by clicking on the Design Connection button.
The OleDB data connection dialog will look like this
For the data source type, you can use the computer’s 64 bit OleDB types. A list is shown in the drop-down that is acquired from your local machine.
The list is dependent on the software applications loaded onto the machine you are using
Once you have selected the driver, enter the server source and details, or the DBQ file if the driver is for Excel or Access.
If you enter details into the OleDB connect string, it will populate the other detail fields 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.