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?

enter image description here


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.

  • 1
    You can't control which core a connection's workload runs on. You can control IF it goes parallel (using MAXDOP) and you can control cost at which it will go parallel (CTFP). – p w Jul 24 '15 at 7:27
  • You're speaking from a SQL Server perspective, correct? From an Informatica perspective, I'm wondering if we need 4 separate connections for the same database, and then use a different connection for each command task? – MacGyver Jul 24 '15 at 15:34

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!

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.