You can add pre- and post-session SQL commands on the Properties tab in the Source Qualifier transformation. You might want to use pre-session SQL to write a timestamp row to the source table when a session begins.
The Integration Service runs pre-session SQL commands against the source database before it reads the source. It runs post-session SQL commands against the source database after it writes to the target.
You can override the SQL commands in the Transformations view on the Mapping tab in the session properties. You can also configure the Integration Service to stop or continue when it encounters errors running pre- or post-session SQL commands.
Use the following guidelines when you enter pre- and post-session SQL commands in the Source Qualifier transformation:
Use any command that is valid for the database type. However, the Integration Service does not allow nested comments, even though the database might.
You can use parameters and variables in source pre- and post-session SQL commands, or you can use a parameter or variable as the command. Use any parameter or variable type that you can define in the parameter file.
Use a semicolon (;) to separate multiple statements. The Integration Service issues a commit after each statement.
The Integration Service ignores semicolons within /*...*/.
If you need to use a semicolon outside of comments, you can escape it with a backslash (\). When you escape the semicolon, the Integration Service ignores the backslash, and it does not use the semicolon as a statement separator.
The Designer does not validate the SQL.
You can also enter pre- and post-session SQL commands on the Properties tab of the target instance in a mapping.