June 5, 2015 at 1:35 am
I have 10 textfiles with this naming convention
ABCD.TXT,1234.TXT,DFGH.TXT.....HERH.TXT
I need to attach these files and send them to the respective emailids as underneath.
How can I achive this in SSIS without script task.
Thanks
June 5, 2015 at 3:19 am
Create a table to add the file names into it e.g.
CREATE TABLE EmailTask(Name VARCHAR(20))
INSERT INTO EmailTask(Name)
SELECT 'ABCD'
UNION
SELECT 'HERH'
...etc
Create a 'object' variable called 'objName' then a string variable called 'strName'.
Use 'Execute SQL Task', and in the editor on the main tab write a sql statement to extract these names. e.g SELECT DISTINCT [Name] FROM EmailTask. Set 'ResultSet' to 'Full result set'. Go to the result set tab and add in 'objName'.
Now add a foreach loop container. In the editor, on the collection tab, set the enumerator type to 'Foreach ADO' and select 'objName', and under 'Enumeration mode' tick 'Rows in first table'.
Go to the variable mappings tab and add 'strName'.
Then just pop a send mail task inside the loop container and configure it. In the editor, go to expressions. Add a property for 'To' and 'Attachments' and build an expression for both. E.g 'To' might be @[User::Name] + "@gmail.com", and 'Attachments' might be "C:\\EmailAttachments\\" + @[User::Name]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy