Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration Connectors
  • All Products

Table of Contents

Search

  1. Preface
  2. Introduction to Microsoft SQL Server Connector
  3. Connections for Microsoft SQL Server
  4. Synchronization tasks with Microsoft SQL Server Connector
  5. Mappings and mapping tasks with Microsoft SQL Server Connector
  6. Replication tasks with Microsoft SQL Server
  7. Data type reference

Microsoft SQL Server Connector

Microsoft SQL Server Connector

Back Next

Microsoft SQL Server targets in a mapping

Microsoft SQL Server targets in a mapping

To write data to a Microsoft SQL Server database, configure a Microsoft SQL Server object as the Target transformation in a mapping. You can use the full
SQL ELT optimization
 to write data to Microsoft SQL Server.
Specify the name and description of the Microsoft SQL Server target. Configure the target and advanced properties for the target object.
The following table describes the target properties that you can configure for a Microsoft SQL Server target:
Property
Description
Connection
Name of the target connection.
You can select an existing connection, create a new connection, or define parameter values for the target connection property.
If you want to overwrite the target connection properties at runtime, select the
Allow parameter to be overridden at run time
 option.
Target Type
Type of the Microsoft SQL Server target object available. You can choose from the following source types:
  • Single Object
  • 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 target object or click
New Parameter
 to define a new parameter for the target object.
The
Parameter
 property appears only if you select parameter as the target type.
If you want to overwrite the target object 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 target object based on target type selected.
Specify the target object that you want to create at run time. You can also select an existing object from the list.
Operation
Select the target operation. You can perform the following operations on a Microsoft SQL Server target:
  • Insert
  • Update
  • Upsert (Update or Insert)
  • Delete
  • Data Driven
Data Driven Condition
Enables you to define expressions that flag rows for an insert, update, or delete operation. Appears only when the operation type is
Data Driven
.
To configure a data driven expression in a mapping, you need to specify the expression that uses the IIF function.
Update Columns
Click
Add
 to select the columns that you want to use as a logical primary key for performing update, upsert, delete, and data driven operations on the target. This field is not required if the target table already has a primary key.
Truncate target
When you enable the
Truncate Target
 option, the Secure Agent truncates the table before running the task. Default is not selected.
Enable target bulk load
Uses Microsoft SQL Server bulk API to insert data in bulk mode.
When you create a
mapping
 task, you can use the Microsoft SQL Server bulk API to perform insert operation.
Forward Rejected Rows
Determines whether the transformation passes rejected rows to the next transformation or drops rejected rows.
If you select the option, the Secure Agent flags the rows for reject and writes them to the reject file. If you do not select the option, the Secure Agent drops the rejected rows and writes them to the session log file. The Secure Agent does not write the rejected rows to the reject file.
Pre SQL
Pre-SQL command to run against the target database before writing data to the target.
Post SQL
Post-SQL command to run against the target database after writing data to the target.
Update Override
An update SQL statement that updates the data in a Microsoft SQL Server target table. The update SQL statement you specify overrides the default update statements that the Secure Agent generates to update the target based on key columns. You can define an update override statement to update target tables based on both key or non-key columns.
In the override statement, you must enclose all reserved words in quotation marks.
Reject file directory
The directory that stores the rejected files.
Reject filename
Name of the rejected file that is stored in the reject file directory.
Schema Name
Overrides the schema name of the target object.
0 COMMENTS
We’d like to hear from you!
Sam Greene - December 21, 2023

Concerning the 'Operation' row, what does 'data driven' mean?

Informatica Documentation Team - December 21, 2023

Hi Sam Greene,

We're working to address your comments and will get back to you.

Thanks,

Informatica Documentation team

Informatica Documentation Team - December 22, 2023

Hi Sam Greene,

The data driven operation works with data driven condition where you can set an expression to flag rows for an insert, update, or delete operation. To configure a data driven expression in a mapping, specify an expression that uses the IIF function.

For more information about the IIF function, see IIF.

Thanks,

Informatica Documentation team

Sam Greene - December 21, 2023

Concerning the 'Operation' row, how does upsert choose the identity column(s)?  Or how does it work if not through identities?

Informatica Documentation Team - December 21, 2023

Hi Sam Greene,

We’re working to address your comments and will get back to you.

Thanks,

Informatica Documentation team

Informatica Documentation Team - December 22, 2023

Hi Sam Greene,

An update column is used as a logical primary key for performing update, upsert, delete, and data driven operations on the target. This field is not required if the target table already has a primary key. If defined, this value takes the precedence.

For more information, see update columns for relational targets.

Thanks,

Informatica Documentation team