See this question for reference.
Can data streams from Informatica to SQL Server be multi-threaded?
I've got most of my problem figured out. I've split up the one large table into four smaller tables. I'm now moving the data from the four source tables to four target tables. Each in their own mapping. In Workflow Manager, each mapping is a separate session, inside of its own workflow. And then I have a master workflow with four command tasks calling the four workflows.
How do I isolate the connections so that each command connects to the target SQL Server database, so that each runs on a different processor?
EDIT:
Informatica also has functionality called "Partitioning", which does exactly what I did here. It splits the data up however you want. But there is some Informatica server level setup for this to work.
Best Answer
When the "Max Degree of Parallelism" configuration in SQL Server (server properties > Advanced) to 0, the server will split up the connections to use as much of the server as it can. With the way I set up the commands in Informatica, there are four separate processes. When I tested on a 2 core server, it used two cores. When I tested on a 4 core server, it used 4 cores. Everything was running in parallel, or asynchronously. It made the ETL process fly!