I have a SQL Server target table (DRS stage) containing CustomerNumberSK and ParentCustomerNumberSK columns.
The ParentCustomerNumberSK is derived earlier in the job stream via a table self-join. ParentCustomerNumberSK can be repeated and can be null. IOW, the ParentCustomerNumber (natural key) is optional; if specified, it should match an existing CustomerNumber, which may be on another row (and usually is).
The target table has a "same table" FK constraint such that the CustomerNumberSK must exist for the ParentCustomerNumberSK to be written out. The ParentCustomerNumberSK will always be present in the incoming data...it just may be "further down" in the table. This situation is causing row rejects due to the FK constraint.
For example:
Code: Select all
CNSK PCNSK Rejected?
1 null N
2 3 Y
3 3 N
4 3 N
5 7 Y
6 7 Y
7 7 N
If I submit "similar" code from the SQL Server client, it works:Transaction size. Specifies the number of rows written before a
commit is executed in the database. A value of 0 causes all rows in
the job to be written as a single transaction.
Code: Select all
INSERT INTO TargetTable
(CustomerNumberSK, ParentCustomerNumberSK)
SELECT
CustomerNumberSK, ParentCustomerNumberSK
FROM StagingTable
Questions:
1. Any ideas on how to get DS to commit the rows in one transaction? I assume this is what the SQL Server client is doing.
2. I find the doc woefully lacking in defining Array Size, Transaction Size, and Transaction Isolation. Are there better docs explaining what these do? Or is this a "read your database documentation" issue?
Thanks,
Scott