September 13, 2011 at 12:11 pm
Hi all,
This is my first time working with SSIS and I'm still reading up on the subject so please bare with me and ask if I need to provide greater detail.
What I'm trying to do is execute a bat file that contains sqlcmd scripts to update a client database. The sqlcmd script has syntax to create the logs on the fly. The issue I'm running into is find a way to email the logs created as flat files to the DBA on call to ensure the update was successful.
The sqlcmd script inside the bat file are as follows:
MKDIR \\localfolder\sqllogs\
SQLcmd -S SERVER -U USER -P PASSWORD -d DATABASE -e -i "\\scriptfolder\TEST-VW.DDL.SQL" >> \\localfolder\sqllogs\TEST-VW.DDL.log
First the sqllogs folder is created then the script is run producing the log.
I have this setup as a Execute Scripts Task in SSIS. I have a variable created for the outputvariable which I was hoping would later be used in Send Email Task but this only send the syntax from the bat file. What I would like to happen is the scripts are execute and all flat files that are created containing the logs are emailed as attachments. How can I declare these logs as variables and send as attachements?
Thanks all!
September 14, 2011 at 7:57 am
What you can do is use a Script Task. Within the Script Task, use the DirectoryInfo.GetFiles("*.log") command to create an enumerator with all the files in the directory. Then, use a For Each File as FileInfo In loop, and loop through the files, using the File.FullName method to get the file names. Create a string outside of the loop, and append to that string the file names, separated by a pipe ("|").
Once the thing is built fully, make sure to remove the trailing or leading pipe, depending on how you set the string up. Put that value in as a ReadWrite variable. Then use that variable as the AttachmentPath expression for the Send Mail Task.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply