Microsoft SQL Server Connector

Back Next

Microsoft SQL Server sources in a mapping

To read data from a Microsoft SQL Server database, configure a Microsoft SQL Server object as the Source transformation in a mapping.

Enable partitioning when you configure the Source transformation in the Mapping Designer to optimize the performance of the

mapping

task.

You can use the source or full

SQL ELT optimization

to read data from Microsoft SQL server.

Specify the name and description of the Microsoft SQL Server source. Configure the source, query options, and advanced properties for the source object.

The following table describes the source properties that you can configure for a Microsoft SQL Server source:

Property	Description
Connection	Name of the source connection. You can select an existing connection, create a new connection, or define parameter values for the source connection property. If you want to overwrite the source connection properties at runtime, select the Allow parameter to be overridden at run time option. Specify the parameter file directory and name in the advanced session properties.
Source Type	Type of the Microsoft SQL Server source object available. You can choose from the following source types: Single Multiple Query Parameter
Parameter	A parameter file where you define values that you want to update without having to edit the task. Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select parameter as the source type. If you want to overwrite the parameter at runtime, select the Allow parameter to be overridden at run time option. When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Object	Name of the Microsoft SQL Server source object.
Filter	Configure a simple filter or an advanced filter to remove rows at the source. You can improve efficiency by filtering early in the data flow. A simple filter includes a field name, operator, and value. Use an advanced filter to define a more complex filter condition, which can include multiple conditions using the AND or OR logical operators. When you configure partitioning, separate multiple filter conditions with parentheses ().
Sort	Select the fields and type of sorting to use. To sort data for a parameterized source, you must use a parameter for the sort options.
Select distinct rows	Select this option to extract only distinct rows.

The following table describes the advanced source properties that you can configure for a Microsoft SQL Server source:

Property	Description
Tracing level	Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Pre SQL	Pre-SQL command that must be run before reading data from the source.
Post SQL	Post-SQL command that must be run after reading data from the source.
SQL Override	The SQL statement to override the default query generated from the specified source type to read data from the Microsoft SQL Server source. You must ensure that the number of columns, order of columns, and the data type you specify in the query matches with the Microsoft SQL Server source object.
Output is Deterministic	Relational source or transformation output that does not change between session runs when the input data is consistent between runs. When you configure this property, the Secure Agent does not stage source data for recovery if transformations in the pipeline always produce repeatable data.
Output is repeatable	Relational source or transformation output that is in the same order between session runs when the order of the input data is consistent. When output is deterministic and output is repeatable, the Secure Agent does not stage source data for recovery.

Rename Saved Search

Table of Contents

Microsoft SQL Server Connector

Microsoft SQL Server Connector

Microsoft SQL Server sources in a mapping

Microsoft SQL Server sources in a mapping