Using SQLCMD to execute SQL scripts from a file on SQL server machine in Informatica

Using SQLCMD to execute SQL scripts from a file on SQL server machine

I was wondering how to execute a SQL script  using Informatica cloud on SQL server. The obvious approach that came to mind was using the SQL transformation in script mode. That approach works but cannot be implemented in Informatica cloud since SQL transformation is not supported in Informatica. The next approach that came to mind was to use some tool that can execute SQL script on SQL server. Happened to find out about this tool called the SQLCMD that can do the task for you.
You can put all the commands you want to execute in a .sql file or .txt file and then execute the script using the SQLCMD utility. In the below example, all the SQL  commands are in C;\test_sql.txt

You will most likely find the tool in the below location on the machine that has microsoft managment studio installed.

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Bin

If you want to execute the SQLCMD command in window authentication mode use the below command:

SQLCMD -S myservermachine -i C:\test_sql.txt -o C:\out.txt

If you want to execute the SQLCMD command in window authentication mode as a different user then use  the below command:

RUNAS /user:Mydomain\myusername "SQLCMD -S myservermachine -i C:\test_sql.txt -o C:\out.txt"

If you want to execute the SQLCMD command in sql server  authentication mode use the below command:

SQLCMD -U myusername -P mypass -S myservermachine -i C:\test_sql.txt -o C:\out.txt

Using NZSQL to execute SQL scripts from a file on Netezza machine

Similary nzsql tool can be used SQL scripts from a file on Netezza machine. The command is:

nzsql -f <sql_file_name>