August 21, 2009 at 11:24 pm
1. I have a file which has three types of records. Header, Trailer and Detail.
2. I want to get header record and insert it into table A.
3. I want to get value of one field (contrive key) from table A.
4. I want to load trailer and Detail records into table B which also requires the contrive key obtained in step 3 above.
5. Solution we have used is:
i. Do a conditional split to get header record in transformation T1
ii. Load the header.
iii. Get the contrive key into a variable.
iv. Create another transformation T2.
v. Do a conditional split for trailer and detail.
vi. Insert record into table B in transformation T2.
Problem here is Architect is not agreeing to this approach of T1 and T2. He is saying that all these steps have to be done in one transformation T1 itself.
Is there any way we can do this?
August 22, 2009 at 4:54 am
There may be another way.
In your source data, are the records linked by keys? Ie, is there already a candidate PK on your source header record? If there is, you could load all of the source header records into table A, including the existing candidate key.
The load the table B records, using a lookup on table A via the candidate key to retrieve the new keys.
This would require two dataflows, each containing a conditional split (dataflow 1 for the header records, dataflow 2 for the rest). Whether that is any better than your method is debatable. What is the architect's objection?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 23, 2009 at 11:17 pm
Provided there is a primary key candidate in the data itself then you can do it in one pass. If not then you will almost certainly have to read the data twice. Let me explain.
Using a conditional split will allow you to split the records off, that isn't a problem, the problem comes when trying to get the key to do the insert into table B. You can't guarantee that it will be available when the lookup is attempted. The reason is that since the rows are batched you can't guarantee the header has been written yet.. I was just looking on CodePlex for a control I thought might help with this, but the one I was thinking of wasn't right. To guarantee the header is written AND the key is available you have to do that in one dataflow and then re-read the data and dump the header rows (since you already dealt with them). At this point you can have a fairly reasonable certainty that the header rows, and thus the keys, are available.
There may be a way but you sacrifice a large amount of performance to do it and I wouldn't recommend it..
In this case I believe your Architect is mistaken and for the reasons I stated. It might be possible but not a good idea.
CEWII
August 25, 2009 at 3:51 pm
I think 2 data flows is the way to go here. With what Elliot said in terms of how SSIS batches the rows through the pipeline, you can't do it one data flow and be sure that the parent rows exist when you go to lookup the key values for the child row inserts.
You can either run through 2 data flows and read in the source file twice, once for each data flow. Or you can use the raw file destination to store off only the child row portion of the file so the second data flow only looks at the child rows and does not have to conditionally split them out from non-detail rows. The Raw File destination is the recommended way to 'share' data between data flows.
edit.
If your architect is worried about atomic transactions, you should be able to wrap the 2 data flows into a container and set the Transaction Option to 'Required' on the container which will ensure that the Header and Detail inserts will be as one transaction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply