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