Execute SQL Task to send a mail if any steps in the SSIS package takes more that 10 mins to execute (Network Steve Forum)

Execute SQL Task to send a mail if any steps in the SSIS package takes more that 10 mins to execute

Hi, I am developing a SSIS package. My business requirement is that, if any steps in the SSIS package takes more that 10 mins to execute then it should send mail to concerned person using Execute SQL Task or Script task. I am developing multiple packages and each package is having multiple steps. So my aim is to use the common logic everywhere. I will really appreciate if someone answer with code sample. Thanks Ghosh

January 27th, 2011 5:16pm

I would advocate the following approach: At the beginning of a package execution place Execute SQL Task that would signal about the package start, e.g. a new entry in a status table with a time stamp + package name; Add an Execute SQL Task (could be a Script Task) to log the start event of the step that needs to be measured (before it gets called) for the entry above; Create your external monitoring solution (e.g. a Windows Service) that will check for the package steps status. In short the logic is if the started step has not completed within the predefined timeframe - send the email. That will be done by the Windows Service. An attempt to interrupt the SSIS execution (say in the middle of a DFT that say loads 10M records) to check if the steps runs longer than x minutes is not feasible.Arthur My Blog

Free Windows Admin Tool Kit Click here and download it now

January 27th, 2011 11:27pm

HI ArthurZ Sorry for posting my question in this link. but i need help from you we have an utility that uploads the data to Shared folder by reading the data from Oracle database - the data in the database is Korean first the data is exported into Flat File and this file becomes an input to Conversion tool.. Here is the snap shot of data in Flat File (csv) file Text qualifier : " Delimiter: , "199963","10","1","2009-03-31","","480000.000","KRW","95327-0 ìœ¡ìƒìš´ì†¡ë¹„-ìš´ì†¡ë£Œ ","020700","","","","1168","1001","","1358750","KOREA" "199963","4","1","2009-03-31","","480000.000","KRW","95629-0 ìœ¡ìƒìš´ì†¡ë¹„-ìš´ì†¡ë£Œ ","020700","","","","1168","1001","","1358750","KOREA" "199963","25","1","2009-03-31","","-887300.000","KRW","95327-0 ìœ¡ìƒìš´ì†¡ë¹„-ë³´ê´€ë£Œ ","020700","","","","1168","1001","","1358750","KOREA" "199963","27","1","2009-03-31","","-573200.000","KRW","95327-0 ìœ¡ìƒìš´ì†¡ë¹„-ê¸°íƒ€ë¹„ìš© ","020700","","","","1168","1001","","1358750","KOREA" "199963","28","1","2009-03-31","","-379609.000","KRW","95327-0 ìœ¡ìƒìš´ì†¡ë¹„-ê¸°íƒ€ë¹„ìš© ","020700","","","","1168","1001","","1358750","KOREA" This is the code that is used in conversion utility ---------------------------------------------------------- strDataFile -- source file containing above data 'Reading CSV as Korean file Using s As StreamReader = New StreamReader(strDataFile, System.Text.Encoding.GetEncoding(949), False) ' Read one line from file unicodeString = s.ReadToEnd() End Using End If 'Get Bytes rbyteShiftJIS = utf8WithoutBom.GetBytes(unicodeString.ToString()) ' Get String Without BOM decodedString = utf8WithoutBom.GetString(rbyteShiftJIS) 'overwrite the file with UTF-8 Encoded data Using w As StreamWriter = New StreamWriter(strDataFile, False, utf8WithoutBom) w.Write(decodedString) over writing the same file with utf8 encoding after this process the lines are as follows ------------------------------------------------ "199963","10","1","2009-03-31","","480000.000","KRW","95327-0 ????? ,"020700","","","","1168","1001","","1358750","KOREA" "199963","25","1","2009-03-31","","-887300.000","KRW","95327-0 ?????," 020700","","","","1168","1001","","1358750","KOREA" "199963","26","1","2009-03-31","","-10000.000","KRW","95327-0 ????? ,"020700","","","","1168","1001","","1358750","KOREA" "199963","27","1","2009-03-31","","-573200.000","KRW","95327-0 ???? ","020700","","","","1168","1001","","1358750","KOREA" "199963","28","1","2009-03-31","","-379609.000","KRW","95327-0 ???? ","020700","","","","1168","1001","","1358750","KOREA" Here for the Bold one you can notice that first 3 lines dose not have double quotes (") at the end.(For the bold text the ending text qualifier is missing) and for the last 2 line there are double quotes("). i am not sure why this is happening.. can any one please help me in retaining the quotes at the end after encoding it to UTF8withoutBOM I guess there is something that is suppressing the quotes if the description column has "?" as a last character. Thanks in Advance

February 9th, 2011 5:43am

Free Windows Admin Tool Kit Click here and download it now

February 9th, 2011 5:43am

This topic is archived. No further replies will be accepted.