Data Source Wizard

• Jan 23, 2019
• 5 minutes to read

The Data Source Wizard allows the user to create a new data source, configure it, and if there is more than one data source available, to select the one that is required for a particular task. Once created, the data source configuration will be stored in the XLS/XLSX spreadsheet file, so the user does not have to re-configure the data source again.

To invoke the wizard, click Add Data Source in the External Data Sources group of the Mail Merge tab.

The Data Source Wizard is invoked. Proceed with the following steps in the wizard.

1. On the first page of the invoked Data Source wizard, specify a name for the new data source and click Next.

   

2. On the next page, select a data source type (Database) and click Next.

   

3. If a data connection has already been specified in the application, the next wizard page allows you to select whether to use one of the existing connections, or create a new one.

   

4. On the next page, select a data provider, specify the required connection settings and click Next.

   

   You can select from the following supported data source types.

   Relational Database System	Supported Versions	.NET Framework Data Provider	.NET Standard 2.0 Data Provider
   Microsoft SQL Server	2005 or higher 2005 Express or higher Azure SQL Database	System.Data.SqlClient.dll System.Data.dll (Included in .NET Framework)	System.Data.SqlClient.dll
   Microsoft Access	Access 2000 or higher Access 2007 or higher	System.Data.dll (Included in .NET Framework)
   Microsoft SQL Server CE	3.5, 4.0	System.Data.SqlServerCe.dll (Included in .NET Framework)
   Oracle Database	9i or higher	Oracle.ManagedDataAccess.dll System.Data.OracleClient.dll Oracle.DataAccess.dll	Oracle.ManagedDataAccess.Core.dll
   Amazon Redshift	n/a	Npgsql.dll	Npgsql.dll
   Google BigQuery	Only legacy SQL functions and operations are supported	DevExpress.DataAccess.BigQuery.dll	DevExpress.DataAccess.BigQuery.dll
   Teradata	13.0 or higher	Teradata.Client.Provider.dll	Teradata.Client.Provider.dll
   SAP Sybase Advantage	Advantage Database Server 9.1 or higher	Advantage.Data.Provider.dll
   SAP Sybase ASE	Sybase Adaptive Server 12.0 or higher	Sybase.AdoNet4.AseClient.dll
   SAP SQL Anywhere	11 or higher	Sap.Data.SQLAnywhere.dll
   IBM DB2	9.5 or higher	IBM.Data.DB2.dll	IBM.Data.DB2.Core.dll
   Firebird	1.5 or higher, Dialect 3	FirebirdSql.Data.FirebirdClient.dll FirebirdSql.Data.Firebird.dll	FirebirdSql.Data.FirebirdClient.dll
   MySQL	4.1 or higher	MySql.Data.dll	MySql.Data.dll
   Pervasive PSQL	9.x or higher	Pervasive.Data.SqlClient.dll
   PostgreSQL	7.x or higher	Npgsql.dll	Npgsql.dll
   VistaDB	4, 5	VistaDB.5.NET40.dll
   SQLite	3.x	System.Data.SQLite.dll Microsoft.Data.SQLite.dll	System.Data.SQLite.Core.dll Microsoft.Data.Sqlite.Core.dll
   XML file	n/a	Built-in support	Built-in support

   Depending on the selected data provider, it may be necessary to specify additional connection options (such as authentication type and database name) on this page.

   Alternatively, you can select Custom connection string and specify the connection string.

   

   Use the XpoProvider parameter to identify a data source provider. See connection string examples in the table below.

   Relational Database System	Connection String Sample See Note
   Microsoft SQL Server *	XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true *The MS SQL Server data provider is automatically specified if you set the “Initial Catalog” parameter and do not use the word “Provider” (for instance, the “XpoProvider” parameter) in the connection string.
   Microsoft Access	XpoProvider=MSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=; XpoProvider=MSAccess;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.accdb;User Id=admin;Password=;
   Microsoft SQL Server CE	XpoProvider=MSSqlServerCE;Data Source=MyDatabase.sdf;Password=MyPassword
   Oracle Database	XpoProvider=Oracle;Data Source=TORCL;User ID=MyUserName;Password=MyPassword XpoProvider=ODP;Data Source=TORCL;User ID=MyUserName;Password=MyPassword XpoProvider=ODPManaged;Data Source=TORCL;User ID=MyUserName;Password=MyPassword
   Amazon Redshift	XpoProvider=Amazon Redshift;Server=127.0.0.1;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Encoding=UNICODE
   Google BigQuery	XpoProvider=BigQuery;ProjectID=myProject;DataSetId=myDataSet;OAuthClientId=myClientId;OAuthClientSecret=mySecret;OAuthRefreshToken=myRefreshToken XpoProvider=BigQuery;ProjectId=project;DatasetId=dataset;ServiceAccountEmail=[e-mail];PrivateKeyFileName=key.p12
   Teradata	XpoProvider=Teradata;Data Source=myServerAddress;User ID=myUsername;Password=myPassword;
   SAP Sybase Advantage	XpoProvider=Advantage;Data Source=\myserver\myvolume\mypat\mydd.add;ServerType=local;User ID=ASSSYS;TrimTrailingSpaces=true
   SAP Sybase ASE	XpoProvider=Ase;Port=5000;Data Source=MyAseServer;User ID=MyUserName;Password=MyPassword;Initial Catalog=MyDatabase;Persist Security Info=true
   SAP SQL Anywhere	XpoProvider=Asa;eng=server;uid=user;pwd=password;dbn=database;persist security info=true;
   IBM DB2	XpoProvider=DB2;Server=server:port;Database=database;UID=user;PWD=password;
   Firebird	XpoProvider=Firebird;DataSource=localhost;User=SYSDBA;Password=masterkey;Database=MyDatabase.fdb;ServerType=0;Charset=NONE
   MySQL	XpoProvider=MySql;Server=MyServerAddress;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Persist Security Info= true;Charset=utf8
   Pervasive PSQL	XpoProvider=Pervasive;Server=MyServerAddress;UID=MyUserName;PWD=MyPassword;ServerDSN=MyDatabase
   PostgreSQL	XpoProvider=Postgres;Server=127.0.0.1;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Encoding=UNICODE
   VistaDB	XpoProvider=VistaDB;Data Source=C:\mydatabase.vdb4 XpoProvider=VistaDB5;Data Source=C:\mydatabase.vdb5
   SQLite	XpoProvider=SQLite;Data Source=filename
   XML file	XpoProvider=InMemoryDataStore;data source=D:\Contacts.xml;read only=True

   Note

   The connection strings demonstrated above are examples. You can specify alternative variants.

   To proceed to the next wizard page, click Next.

5. On the next page you are prompted to save the connection string including or excluding the user name and a password. Choose any option and click Next.

6. This page allows you to select a query or a stored procedure.

   Click Run Query Builder… to invoke the Query Builder window.

   

   Important

   Manual SQL editing is not allowed by default. Enabling custom SQL queries may lead to inadvertent or unauthorized modifications to your data/database structure. Before you decide to allow manual SQL editing, we recommend you to follow best practices and implement the appropriate end-user read/write permissions at the database level.

   If for some reason you need to enable custom SQL editing, set the SpreadsheetControl.Options.DataSourceWizard.EnableCustomSql option to true.

   Note that only SELECT statements are allowed in the text. To override this restriction, handle the SpreadsheetControl.ValidateCustomSqlQuery event.

7. In the invoked Query Builder window, double-click the Categories item in the list of available tables on the left to add it to the tables included into the query.

   Enable the check box for the Categories table, to include all of its fields in the view.

   

   Click OK to close the Query Builder and return to the Data Source Wizard.

8. When the query is ready, click Finish to save it.

   

The Field List Panel displays the structure of the created data source.

To modify the query used in the data source, click Manage Relations -> Manage Queries in the External Data Sources group.

Tip

The data source configuration is stored in .xls or .xlsx file. The end-user can save the workbook after creating the data source. Next time, the user can start performing a mail merge immediately after the XLSX/XLS file is loaded into the Spreadsheet control.