Deadlock problem? 3 way conditional split of data from one table to another never completes

I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete.

Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.

Aren't you using a union all transformation before the destination to bring your streams back together?|||

If your situation allows it simply uncheck the destination table option "Table Lock" and it will work.

Philippe

|||That was it! Thanks.|||Did not try that. Is that the recommeded technique to use in this situation?|||

Great, just make sure that the union all task is not better appropriate.

I use the do not lock table option only on tables that I kow for sure no other process is trying to update and or insert into.

And I do this at a time of night when nothing is accessing the table. Preferably against a staging table that will replace the production table using either sp_rename or things like that.

Philippe

|||

Jeff-B wrote:

Did not try that. Is that the recommeded technique to use in this situation?

If you were previously using separate destination connectors for the same table, then yes, that would be the recommended technique.

|||

I'd agree with Phil and Philippe - a UNION ALL component is the better way to go. It will be more performant too because there is only one insertion operation.

-Jamie

|||Would this still be the case if you were using different derived fields or different source table fields for each source to populate the fields of the target table. Does the UNION ALL allow for mapping of each source to the target or does each source to the UNION ALL have to have the same set of fields?|||You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.|||

Phil Brammer wrote:

You can "join" disparate sources as long as they are the same data type. That's the idea of a union, just to bring data together, but not to necessarily join it. Traditionally, unions contain many NULL fields as a result.

I also want to clarify that if your different data flows were going to the same physical table, then yes, a union all transformation is what you want. It'll work, trust me! Come back here if you have issues with it.|||Thanks Phil. I think I understand how to use this feature now. I'll experiment and see if I achieve the same result with the 4 independent paths to the same table.