Email logs from sqlcmd executed through SSIS

• amaines

  SSC Veteran

  Points: 274

  More actions

  September 13, 2011 at 12:11 pm

  #243685

  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!

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  September 14, 2011 at 7:57 am

  #1381305

  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.