SSIS Send email to different recipients with different data from SQL Table
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 '18 at 5:18
kenlukas
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
add a comment |
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 '18 at 5:18
kenlukas
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12
add a comment |
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis edited Nov 13 '18 at 5:18
kenlukas
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
I am trying to create an SSIS Package that sends out emails to different recipients and includes some of the data as a table that is either included in the body of the email or attached as an Excel file.
I have three tables, one that includes the email addresses, one that includes the error type and the main table that contains customer information like name, old address info, and new address info all broken out into about 16 different columns, of which are codes to pull from the two other tables to determine which person to email to update the address and a code description on why they need to update it.
I have created a script task to combine the data and pass it to a foreach loop container.
I'm not sure how to take the customer info, old address and new address columns of data from what I've pulled together in the sql task (CIF_Nbr to Current_XIPplus4) and create a table from it that can be inserted into the body or added as an attachment to the send mail task.
A branch could have multiple lines of data so I would prefer one attachment for all same branches sent only once to that branch.
My three tables have the following columns:
I'm using Microsoft Visual Studio 2008 and SQL server 2008 if that helps. I'm pretty much stuck with the default toolbox.
Hopefully this all makes sense. I'm fairly new to visual studio so the more detail the better. Thanks.
sql-server ssis sql-server ssis edited Nov 13 '18 at 5:18
kenlukas
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
edited Nov 13 '18 at 5:18
kenlukas
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
edited Nov 13 '18 at 5:18
kenlukas
1,28731317
edited Nov 13 '18 at 5:18
kenlukas
1,28731317
edited Nov 13 '18 at 5:18
kenlukas
1,28731317
1,28731317
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
asked Nov 12 '18 at 22:09
Shaun RezacShaun Rezac
1
1
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12
add a comment |
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12
add a comment |
1 Answer
1
active
oldest
votes
I decided to take this a different direction since this was being used on more of an adhoc basis.
Instead of SSIS I set it up in excel using power query to pull the data together from different sources and some vba code found at Ron Debruin's site http://www.rondebruin.nl/ to parse out and email each group needing to make an update.
Thanks to all for the help provided.
Let me know if you have any questions.
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
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%2f53270820%2fssis-send-email-to-different-recipients-with-different-data-from-sql-table%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
I decided to take this a different direction since this was being used on more of an adhoc basis.
Instead of SSIS I set it up in excel using power query to pull the data together from different sources and some vba code found at Ron Debruin's site http://www.rondebruin.nl/ to parse out and email each group needing to make an update.
Thanks to all for the help provided.
Let me know if you have any questions.
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
add a comment |
I decided to take this a different direction since this was being used on more of an adhoc basis.
Instead of SSIS I set it up in excel using power query to pull the data together from different sources and some vba code found at Ron Debruin's site http://www.rondebruin.nl/ to parse out and email each group needing to make an update.
Thanks to all for the help provided.
Let me know if you have any questions.
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
add a comment |
I decided to take this a different direction since this was being used on more of an adhoc basis.
Instead of SSIS I set it up in excel using power query to pull the data together from different sources and some vba code found at Ron Debruin's site http://www.rondebruin.nl/ to parse out and email each group needing to make an update.
Thanks to all for the help provided.
Let me know if you have any questions.
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
I decided to take this a different direction since this was being used on more of an adhoc basis.
Instead of SSIS I set it up in excel using power query to pull the data together from different sources and some vba code found at Ron Debruin's site http://www.rondebruin.nl/ to parse out and email each group needing to make an update.
Thanks to all for the help provided.
Let me know if you have any questions.
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
answered Nov 26 '18 at 19:44
Shaun RezacShaun Rezac
1
1
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%2f53270820%2fssis-send-email-to-different-recipients-with-different-data-from-sql-table%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
Please show your table schema as a code snippet and not as screenshots.
– jtate
Nov 12 '18 at 22:14
Here's one example of sending data in an email dba.stackexchange.com/questions/35263/… You can do what you want by repeatedly calling msdb.dbo.sp_send_dbmail. You don't even need SSIS for this.
– Nick.McDermaid
Nov 13 '18 at 11:12