(1) Is it possible to run SQL commands through preprocessing and postprocessing without wrapping them in operating system commands?
Yes, you can execute SQL commands through Pre/Post session command tasks.
(2) If yes to (1), what is the syntax? You need to OSQL/SQLCMD utilities to call SQL commands through command task.
(3) If yes to (1), what endpoints can I connect to in the preprocessing and postprocessing fields? Can both fields hit both the source and target, and if so, what's the syntax for switching between endpoints? We can't get the return values to mapping if you call SQL commands through command task. We should use DDL(create/alter) or DML(insert,update,delete) statements but not select statement.
I wonder how we can use OSQL/SQLCMD utilities in cloud.
Does secure agent manage this option. Any specific steps will be helpful.
1 of 1 people found this helpful
You can run preprocessing and postprocessing commands to perform additional tasks. The Data Synchronization service runs preprocessing SQL commands before it reads the source. It runs postprocessing SQL commands after it
writes to the target.
You can use the following types of commands:
♦ SQL commands. Use SQL commands to perform database tasks.
♦ Operating system commands. Use shell and DOS commands to perform operating system tasks.
If any command in the preprocessing or postprocessing
scripts fail, the Data Synchronization service fails the task.
Rules and Guidelines for Preprocessing and Postprocessing SQL Commands
You can run SQL commands before or after a task. For example, you can use SQL commands to drop indexes on the target before the task runs, and then recreate them when the task completes.
Use the following rules and guidelines when creating the SQL commands:
♦Use any command that is valid for the database type. However, the Data Synchronization service does not allow nested comments, even if the database allows them.
♦Use a semicolon (;) to separate multiple statements. The Data Synchronization service issues a commit after each statement.
♦The Data Synchronization service ignores semicolons within /* ...*/.
♦If you need to use a semicolon outside of comments, you can escape it with a backslash (\).
♦The Data Synchronization service does not validate the SQL.
Give a good example . I want to create an index after Post-processing Commands.
here is my MSSQL Command
create index ind_name on actcustorder_summary(Name)
How do I pass this command in Post processing command ?