SSIS Foreach loop container suggestions
I have not used a foreach loop container before and need some help with this.
I have a SSIS package that at the moment does some stuff (import from excel into database, some merges, some joins).
Basically at the end I finish with a table that contains the following fields (modified):
Invoice number
Container number
Item number
Item price
item duty
(...)
I need it to do some specific SQL (joins, insert formulas ...) for each invoice and export that to an Excel file with file name = invoice number.
I thought of getting a select distinct ROW_NUmber () (...),[Invoice_number] from [invoicetable]
and getting to iterate over the row_number basically and do what I needed it to do.
I've thought about maybe getting this on a SQL loop to create #temp1, #temp2, ... and then export all of the #temp individually and then dropping them.
I am looking for ideas of the most efficient way of getting this done.
I must say I have tried T-SQL to get the files to be exported using cmd but I keep getting errors trying to create the files.
Thanks in advance!
sql-server ssis visual-studio-2017
add a comment |
I have not used a foreach loop container before and need some help with this.
I have a SSIS package that at the moment does some stuff (import from excel into database, some merges, some joins).
Basically at the end I finish with a table that contains the following fields (modified):
Invoice number
Container number
Item number
Item price
item duty
(...)
I need it to do some specific SQL (joins, insert formulas ...) for each invoice and export that to an Excel file with file name = invoice number.
I thought of getting a select distinct ROW_NUmber () (...),[Invoice_number] from [invoicetable]
and getting to iterate over the row_number basically and do what I needed it to do.
I've thought about maybe getting this on a SQL loop to create #temp1, #temp2, ... and then export all of the #temp individually and then dropping them.
I am looking for ideas of the most efficient way of getting this done.
I must say I have tried T-SQL to get the files to be exported using cmd but I keep getting errors trying to create the files.
Thanks in advance!
sql-server ssis visual-studio-2017
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41
add a comment |
I have not used a foreach loop container before and need some help with this.
I have a SSIS package that at the moment does some stuff (import from excel into database, some merges, some joins).
Basically at the end I finish with a table that contains the following fields (modified):
Invoice number
Container number
Item number
Item price
item duty
(...)
I need it to do some specific SQL (joins, insert formulas ...) for each invoice and export that to an Excel file with file name = invoice number.
I thought of getting a select distinct ROW_NUmber () (...),[Invoice_number] from [invoicetable]
and getting to iterate over the row_number basically and do what I needed it to do.
I've thought about maybe getting this on a SQL loop to create #temp1, #temp2, ... and then export all of the #temp individually and then dropping them.
I am looking for ideas of the most efficient way of getting this done.
I must say I have tried T-SQL to get the files to be exported using cmd but I keep getting errors trying to create the files.
Thanks in advance!
sql-server ssis visual-studio-2017
I have not used a foreach loop container before and need some help with this.
I have a SSIS package that at the moment does some stuff (import from excel into database, some merges, some joins).
Basically at the end I finish with a table that contains the following fields (modified):
Invoice number
Container number
Item number
Item price
item duty
(...)
I need it to do some specific SQL (joins, insert formulas ...) for each invoice and export that to an Excel file with file name = invoice number.
I thought of getting a select distinct ROW_NUmber () (...),[Invoice_number] from [invoicetable]
and getting to iterate over the row_number basically and do what I needed it to do.
I've thought about maybe getting this on a SQL loop to create #temp1, #temp2, ... and then export all of the #temp individually and then dropping them.
I am looking for ideas of the most efficient way of getting this done.
I must say I have tried T-SQL to get the files to be exported using cmd but I keep getting errors trying to create the files.
Thanks in advance!
sql-server ssis visual-studio-2017
sql-server ssis visual-studio-2017
edited Nov 20 '18 at 14:10
Jacob H
2,0751626
2,0751626
asked Nov 20 '18 at 13:13
fireshark519fireshark519
1259
1259
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41
add a comment |
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41
add a comment |
2 Answers
2
active
oldest
votes
The way I would do this is use an Execute SQL Task
to populate an object variable with all the invoice numbers in your table.
Then have a foreach loop
that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}
.
Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of yourPO
variable? It needs to be "Object".
– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
add a comment |
I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".
I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.
The C# code is here:
C# Variable Excel file output loop
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%2f53393802%2fssis-foreach-loop-container-suggestions%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
The way I would do this is use an Execute SQL Task
to populate an object variable with all the invoice numbers in your table.
Then have a foreach loop
that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}
.
Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of yourPO
variable? It needs to be "Object".
– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
add a comment |
The way I would do this is use an Execute SQL Task
to populate an object variable with all the invoice numbers in your table.
Then have a foreach loop
that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}
.
Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of yourPO
variable? It needs to be "Object".
– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
add a comment |
The way I would do this is use an Execute SQL Task
to populate an object variable with all the invoice numbers in your table.
Then have a foreach loop
that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}
.
Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.
The way I would do this is use an Execute SQL Task
to populate an object variable with all the invoice numbers in your table.
Then have a foreach loop
that iterates over that object variable and begins with a script task that uses the value of the invoice number to set the name of your destination excel file, and also to generate the SQL Command of your data flow's source component to SELECT from the table WHERE InvoiceNumber={the current invoice number in the loop}
.
Each iteration of the loop will select one invoice and send it to one appropriately-named Excel file. Then move on to the next invoice number.
answered Nov 20 '18 at 19:58
Tab AllemanTab Alleman
26.8k52440
26.8k52440
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of yourPO
variable? It needs to be "Object".
– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
add a comment |
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of yourPO
variable? It needs to be "Object".
– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
ok so I've tried doing this. I set the variable named PO. I have a SQL task as follows: SELECT DISTINCT Purchase_order FROM Staging.InputWOrigin AS iw WHERE (Transfer_Reference = ' ') ResultSet is set to "Full set Result" under the Result Set options I have Result Name 0 and variable Name User::PO. I tried having this task inside or outside the foreachloop but I get this error."The type of the value (DBNull) being assigned to variable "User::PO" differs from the current variable type (String). Variables may not change type during execution.
– fireshark519
Nov 28 '18 at 15:42
What is the data type of your
PO
variable? It needs to be "Object".– Tab Alleman
Nov 28 '18 at 16:29
What is the data type of your
PO
variable? It needs to be "Object".– Tab Alleman
Nov 28 '18 at 16:29
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
Allerman, I've tried that and didn't work. I did it again now for my own sanity and it worked...let me try again the foreach loop tomorrow and I'll see if the rest is working as it should.
– fireshark519
Nov 28 '18 at 16:59
add a comment |
I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".
I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.
The C# code is here:
C# Variable Excel file output loop
add a comment |
I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".
I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.
The C# code is here:
C# Variable Excel file output loop
add a comment |
I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".
I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.
The C# code is here:
C# Variable Excel file output loop
I ended up giving up on the foreach loop container. As the data input to my tables was an excel file I had so many problems with data type conversions in the middle that nothing wanted to work in the loop. I couldn't get it to iterate over the invoice "number/name".
I ended up looking at C# as an option and managed to loop it but could not get it to loop inside the loop initially, after some trial an error I managed to get the outcome I needed and this process is now running what prior BA created with 4 macros, in SSIS package reducing work time from 1800 minutes to 3.
The C# code is here:
C# Variable Excel file output loop
answered Dec 4 '18 at 16:27
fireshark519fireshark519
1259
1259
add a comment |
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%2f53393802%2fssis-foreach-loop-container-suggestions%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
Foreach loop container seems reasonable. Set a variable = to the source connection for the Excel file. Use the variable as the invoice number in a data flow. Loop for each file in the folder.
– Jacob H
Nov 20 '18 at 14:12
@JacobH sorry but by reading your explanation it makes me think you thought I wanted to import the files and not export. Might have understood your explanation wrong but that's what I feel. I get setting the variable in the connection so that the file name is the invoice number.
– fireshark519
Nov 20 '18 at 14:38
My idea is something along the lines of: @InvoiceResult=0 @i=0 InvoiceResult=InvoiceNumber where ROWNUM=(i) set (i)=(i)+1 ; filename variable [@invoiceresult].csv
– fireshark519
Nov 20 '18 at 14:41