SSIS - Process batches using top
I am doing a Fuzzy mapping job on millions of rows of data (From Table A). I take top 10000 a time , process them and store the mapped rows in a different table. (Table B)
Then when I choose the next batch, I make sure that the Ids from table A, which are now in Table B are not picked for processing.
This is working fine.
How do I now get the process to loop through without me having to stop start after every iteration?
Thanks
loops ssis
asked Nov 21 '18 at 16:11
SenevicSenevic
13
add a comment |
I am doing a Fuzzy mapping job on millions of rows of data (From Table A). I take top 10000 a time , process them and store the mapped rows in a different table. (Table B)
Then when I choose the next batch, I make sure that the Ids from table A, which are now in Table B are not picked for processing.
This is working fine.
How do I now get the process to loop through without me having to stop start after every iteration?
Thanks
loops ssis
asked Nov 21 '18 at 16:11
SenevicSenevic
13
add a comment |
I am doing a Fuzzy mapping job on millions of rows of data (From Table A). I take top 10000 a time , process them and store the mapped rows in a different table. (Table B)
Then when I choose the next batch, I make sure that the Ids from table A, which are now in Table B are not picked for processing.
This is working fine.
How do I now get the process to loop through without me having to stop start after every iteration?
Thanks
loops ssis
asked Nov 21 '18 at 16:11
SenevicSenevic
13
I am doing a Fuzzy mapping job on millions of rows of data (From Table A). I take top 10000 a time , process them and store the mapped rows in a different table. (Table B)
Then when I choose the next batch, I make sure that the Ids from table A, which are now in Table B are not picked for processing.
This is working fine.
How do I now get the process to loop through without me having to stop start after every iteration?
Thanks
loops ssis
loops ssis
asked Nov 21 '18 at 16:11
SenevicSenevic
13
asked Nov 21 '18 at 16:11
SenevicSenevic
13
asked Nov 21 '18 at 16:11
SenevicSenevic
13
asked Nov 21 '18 at 16:11
SenevicSenevic
13
asked Nov 21 '18 at 16:11
SenevicSenevic
13
13
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
- Create these variables (SourceMaxID and DestMaxID) as shown below:
Variables
In your Control flow;
Add an Execute SQL Task: in the SQL Statement, use the script below to get the max ID
from your Table A and assign the value to variable SourceMaxID.
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table A]
Configure the result set to single row and add the variable.
Add a For Loop Container to your control flow.
Drag the Data flow Task(s) you created for the fuzzy mapping into the For Loop
Container as shown below.
Add an Execute SQL TASK to the For Loop Container as shown below.
Similar to process 1, get the MaxID from Table B and assign to variable DestMaxID
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table B]
Control flow
- Configure the For Loop Container as shown below:
For loop container
answered Nov 23 '18 at 13:24
iMajekiMajek
434
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416176%2fssis-process-batches-using-top%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
- Create these variables (SourceMaxID and DestMaxID) as shown below:
Variables
In your Control flow;
Add an Execute SQL Task: in the SQL Statement, use the script below to get the max ID
from your Table A and assign the value to variable SourceMaxID.
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table A]
Configure the result set to single row and add the variable.
Add a For Loop Container to your control flow.
Drag the Data flow Task(s) you created for the fuzzy mapping into the For Loop
Container as shown below.
Add an Execute SQL TASK to the For Loop Container as shown below.
Similar to process 1, get the MaxID from Table B and assign to variable DestMaxID
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table B]
Control flow
- Configure the For Loop Container as shown below:
For loop container
answered Nov 23 '18 at 13:24
iMajekiMajek
434
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
add a comment |
- Create these variables (SourceMaxID and DestMaxID) as shown below:
Variables
In your Control flow;
Add an Execute SQL Task: in the SQL Statement, use the script below to get the max ID
from your Table A and assign the value to variable SourceMaxID.
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table A]
Configure the result set to single row and add the variable.
Add a For Loop Container to your control flow.
Drag the Data flow Task(s) you created for the fuzzy mapping into the For Loop
Container as shown below.
Add an Execute SQL TASK to the For Loop Container as shown below.
Similar to process 1, get the MaxID from Table B and assign to variable DestMaxID
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table B]
Control flow
- Configure the For Loop Container as shown below:
For loop container
answered Nov 23 '18 at 13:24
iMajekiMajek
434
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
add a comment |
- Create these variables (SourceMaxID and DestMaxID) as shown below:
Variables
In your Control flow;
Add an Execute SQL Task: in the SQL Statement, use the script below to get the max ID
from your Table A and assign the value to variable SourceMaxID.
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table A]
Configure the result set to single row and add the variable.
Add a For Loop Container to your control flow.
Drag the Data flow Task(s) you created for the fuzzy mapping into the For Loop
Container as shown below.
Add an Execute SQL TASK to the For Loop Container as shown below.
Similar to process 1, get the MaxID from Table B and assign to variable DestMaxID
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table B]
Control flow
- Configure the For Loop Container as shown below:
For loop container
answered Nov 23 '18 at 13:24
iMajekiMajek
434
- Create these variables (SourceMaxID and DestMaxID) as shown below:
Variables
In your Control flow;
Add an Execute SQL Task: in the SQL Statement, use the script below to get the max ID
from your Table A and assign the value to variable SourceMaxID.
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table A]
Configure the result set to single row and add the variable.
Add a For Loop Container to your control flow.
Drag the Data flow Task(s) you created for the fuzzy mapping into the For Loop
Container as shown below.
Add an Execute SQL TASK to the For Loop Container as shown below.
Similar to process 1, get the MaxID from Table B and assign to variable DestMaxID
SELECT ISNULL(MAX(IDColumn), 0) FROM [Table B]
Control flow
- Configure the For Loop Container as shown below:
For loop container
answered Nov 23 '18 at 13:24
iMajekiMajek
434
answered Nov 23 '18 at 13:24
iMajekiMajek
434
answered Nov 23 '18 at 13:24
iMajekiMajek
434
answered Nov 23 '18 at 13:24
iMajekiMajek
434
434
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
add a comment |
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
Thanks Isaac. I will try this out. Currently I have it running on a manually configured For Loop
– Senevic
Nov 27 '18 at 10:05
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
@Senevic How did you get on?
– iMajek
Nov 29 '18 at 12:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53416176%2fssis-process-batches-using-top%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
