SQL Statement Destination

SQL Statement Destination object offers extra flexibility over database destination objects in applying a custom INSERT, UPDATE, or DELETE SQL code that controls what will be written into the destination table. SQL Statement Destination can also be used to call stored procedures. You can parameterize SQL Statement Destination using the Parameter Replacement functionality to supply values at runtime.

SQL Statement Destination supports transaction management and the ability to use Shared Connection objects. Shared Connection objects can be used across multiple destinations, providing transaction management and rollback functionality.

Steps

An example of what an SQL Statement Destination object looks like is shown below:

To configure the properties of an SQL Statement Destination object after it was added to the dataflow, right-click on it and select Properties from the context menu. The following properties are available:

• Database Connection screen – allows you to enter the connection information for your SQL Statement, such as server name, database, and schema, as well as credentials for connecting to the selected database.

• SQL Query screen: In the SQL Query screen, you can enter an SQL expression controlling which fields and records should be written to the destination. The SQL expression should follow standard SQL syntax conventions for the chosen database provider.

For example,

Insert into Orders values (@OrderId, “@OrderName”, “@CreatedDtTm”)

Notice the @ symbol in front of a field name. This makes the field appear in the field list inside the object box so that the field can be mapped. The fields not showing the @ symbol in front of them will not show in the list of fields, but they can still receive values according to the logic of the SQL statement itself.

For example,

"Insert into Orders (OrderId, OrderName, CreatedDtTm) values (@OrderId, “@OrderName”, “2010/01/01”)"

Note: You can optionally use $ parameters inside your SQL expression.