I have a flow created which has data set which has 2 fairly large tables and i have the flow built as follows:

source1 -> sql (select *) -> union->sql->target
source2 -> sql (select *) -> union->sql->taget

and when i try to test the union i get the following error:

(FOC010) THE NUMBER OF SORT FIELDS EXCEEDS THE MAXIMUM: XXXXX(columnname)
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
there are about 176 rows and when i cut the columns down to where the the error says (its the 128th row) then it works

Is the best way to go about this to union and pick and choose or am i missing something else, this is raw data and i would prefer to capture it all?

A 'normal' Union returns a result that contains all rows from both sets with duplicate rows removed. In order to remove the duplicate rows Data Migrator must SORT both input sources, and it is here where you encounter the DM has a maximum of 128 sort fields.

The easiest is if you are sure that the 2 sources do not have duplicate rows, or you do not mind to have those duplicates. In that case change the Union properties to Union Type = Union All

If you do need to remove the duplicate rows there are 2 solutions:
If the Target is loaded as Insert/Update with Update if the record exists, this update will remove the duplicates.

Otherwise I suggest not to use the DataMigrator Union, but make a DBMS SQL flow by which you let the DBMS do the UNION (and remove the duplicates) in stead of DataMigrator.

There is a DataMigrator UNION object but
DM generates SQL SELECT statements and if possible passes them to the underlying relational database for execution.

That would require that all sources are in the same relational database and the same connection.

In this particular instance hotline learned that the two source tables were two different MS SQL Server databases accessed through different connections. Thus the UNION operation is done in FOCUS and hits the limit.

This can be avoided by creating a Remote View in MS SQL Server in one database for the table in the other. Then from the DMC create a synonym for the VIEW. Now the UNION can be passed to MS SQL Server.