SSIS foreach loop container
-
what iam tying to do is load data from tableA and use the for next loop to insert, update and delete records in tableB depending on whats in tableA
so far I can load 100 records and send mail task 100 times inside a fornext loop. I am not sure what variables I need to and how to do it.
See attachment
-
Can I ask a question first? Are you primarily an application developer? I ask only because the method you have described is very procedural. And I think a set based approach is better.
The method I would suggest is very different. You wold use a dataflow component and inside that I would likely use a conditional split to break the data from tableA into different paths for insert, update, and delete. It would also perform much better than what you are suggesting.
How would you detect an insert vs. an update vs. a delete?
CEWII
-
I am .net developer but have been seconded to the dba section.
I have tried to use the conditional split but it grabs the data in batches, the delete update and insert needs to be row by row because some row are updated,deleted and then inserted again. they have to be in the same order as F3. I have tried rbar but with any luck:-D
F1 ¦ F2 ¦ F3 ¦ F4
-----------------------------------------
21 ¦ I ¦ 1 ¦ 10024414482
24 ¦ I ¦ 2 ¦ 10024414482
21 ¦ U ¦ 3 ¦ 10004678137
24 ¦ U ¦ 4 ¦ 10004678137
24 ¦ I ¦ 5 ¦ 10004678137
21 ¦ U ¦ 6 ¦ 10004678205
-
Well that does make things a bit more complex..
Ok, this solution will not perform as well as my first thought, but will still perform pretty good.
Write a sproc that takes the parameters and does the I/U/D, whichever is requested.
Your dataflow needs 2 components. An OLEDB source, with a query that specifies an ORDER BY on the F3 column. This should get the records into pipeline in order. And an OLEDB Command transformation that calls the sproc.
How does that sound?
CEWII
-
Thanks for your solution, being a newbie to SSIS, after processing first row how does it move onto next row without using a for next loop.
-
Since it is in a data-flow component the answser is yes. what happens is the data-source fills the pipeline in the order specified by F3. And that pipeline is fed in order to subsequent components in order.
If you have additional questions please ask..
CEWII
-
i have just been reading up on OLEDB command transformation, could you use this command as it reads row by row with a stored procedure
-
I don't really understand what you have asked.. Sorry..
I have another question. Is the source data and destination location on the same server?
See the image to see what I'm talking about
CEWII
-
yes the source and destination are on the correct server, the diagram you provided is what i have.
Thankyou, I think you answered my questions for now, but iam sure I will be posting again!!!!
-
Hi
I have a problem eith the solution described earlier, the problem being that it iterates through the dataset as many times as there are rows, therefore when it inserts a 1 record into the table because there are 18 instances of the insert with 100 records there are 1800 entries in the table.
What I want is when it completes the first record insert it moves onto the next record
-
I'm confused.
How many records are in the ADO Recordset that the Foreach Loop containter is iterating through? If it is 18 then the container will execute its contents 18 times. I didn't understand where you were going with what you were saying. Where did the 100 rows come from?
CEWII
-
Table A has 100 records with I, U, D (insert, update, delete)records.
18 of the records are need to be inserted, when the stored procedure run's 1800 records are inserted into table B (100 x 18).
I need just 1 record per insert.
I haven't used a for each loop, I used the method you described earlier. How do implement a foreach loop to iterate through the rows
-
Ok, then I think I am starting to see the problem..
Also I'm not recommending a For loop.
It sounds like the recordset being passed into the F-E-L contains too many records. You need to modify your query to only produce the work needed for this pass. This might mean the process tags a processed record as "processed" so you can tell what has been processed and what hasn't.
It also sounds like your sproc is not quite right. It should operate on a single record. If there are 100 records in the recordset being passed into the F-E-L then it will execute 100 times. If it is generating 1800 records it isn't working with a single record.. That sproc should take either all of its inputs from the F-E-L or an ID of the record it is supposed to be working with and pull the single row of data it is supposed to work with.
Is this clear?
CEWII
-
iam trying to iterate through results from ole DB source
the quey below but it inserts 64 records instead of 4,
could you give me an idear where i am going wrong.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[proc_Pro_Order]
-- Add the parameters for the stored procedure here
AS
declare @LastRecordID INT
set @LastRecordID =0
declare @RecordIDToHandle INT
SELECT TOP 1 @RecordIDToHandle = field3
FROM MASTER_TABLE
WHERE field3 > @LastRecordID
ORDER BY field3
WHILE @RecordIDToHandle IS NOT NULL
BEGIN
INSERT INTO BASIC_LAND_AND_PROPERTY_UNIT_RECORD_TYPE21
(Record_Identifier,
Change_Type,
Pro_Order,
UPRN)
Select Field1,Field2,Field3,Field4
From MASTER_TABLE
Where Field1 = '21' AND Field2 = 'I'
set @LastRecordID = @RecordIDToHandle
set @RecordIDToHandle = NULL
SELECT TOP 1 @RecordIDToHandle = field3
FROM MASTER_TABLE
WHERE field3 > @LastRecordID
ORDER BY field3
END
-
I think I see the problem.. It is deciding what record IT wants to work on.. It should accept either an ID for the record it should be working on OR all the data it needs to use should be passed into it.
CEWII
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply