You can use a static database connection or you can pass database connection information to the SQL transformation at run time.
You can use a connection environment SQL statement or transactional SQL statement with the SQL transformation. Configure the SQL statements in a relational connection object. The Integration Service runs the connection environment SQL when it connects to the database. It runs the transactional SQL statement before the initiation of each transaction.
Use one of the following types of connections to connect the SQL transformation to a database:
Configure the connection object in the session. You must first create the connection object in Workflow Manager.
Pass a connection name to the SQL transformation as input data at run time. You must first create the connection object in Workflow Manager.
Full database connection.
Pass the connect string, user name, password, and other connection information to the SQL transformation input ports at run time.
If a session has multiple partitions, the SQL transformation creates a separate database connection for each partition.