SSIS Script Task - Write Log To Disk
E.DIN Editor • November 17, 2021
Purpose: Guaranteed logging on package error, advanced usage of Dts.VariableDispenser
public void LogResults()
{
bool errorOccurred = false; //in event all logging methods fail
Variables lockedVariables = null;
string fileName = "";
string fileData = "";
try
{
lockedVariables = null;
Dts.VariableDispenser.LockOneForRead("OutputDirPath", ref lockedVariables);
Dts.VariableDispenser.LockOneForRead("OutputData", ref lockedVariables);
Dts.VariableDispenser.GetVariables(ref lockedVariables);
fileName = (string)lockedVariables["User::OutputDirPath"].Value;
if (fileName == null || fileName.Length == 0)
{
fileName = @"C:\ScriptTaskResults.log";
}else{
fileName = fileName.Replace(".log", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".log"));
// fileName format ex: \\ssis-server01\FTP\ScriptTaskOutput_20200429011136.log
}
fileData = (string)lockedVariables["User::OutputData"].Value;
if (fileData == null || fileData.Length == 0 || fileData.ToLower().Contains("system.object"))
{
fileData = "OutputData value is empty. Nothing to report.";
}
using (StreamWriter w = File.AppendText(fileName))
{
Log(fileData, w);
}
using (StreamReader r = File.OpenText(fileName))
{
DumpLog(r);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
errorOccurred = true; // possible network logging issue. try to save to event log.
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry(string.Concat("ssis pkg output log issue. ", "\r\n", ex.Message.ToString()), EventLogEntryType.Error, 101, 1);
}
}
finally
{
if (lockedVariables != null) lockedVariables.Unlock();
if (errorOccurred) // fire event to store in SQL DTS log
{
Dts.Events.FireError(18, "ssis pkg output log issue.", "An Exception was caught during logging write and output data flush. Check the server event application log for details.", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
public static void Log(string logMessage, TextWriter w)
{
w.Write("\r\nLog Entry : ");
w.WriteLine($"{DateTime.Now.ToLongTimeString()} {DateTime.Now.ToLongDateString()}");
w.WriteLine(" :");
w.WriteLine($" :{logMessage}");
w.WriteLine("-------------------------------");
}
public static void DumpLog(StreamReader r)
{
string line;
while ((line = r.ReadLine()) != null)
{
Console.WriteLine(line);
}
}
{
bool errorOccurred = false; //in event all logging methods fail
Variables lockedVariables = null;
string fileName = "";
string fileData = "";
try
{
lockedVariables = null;
Dts.VariableDispenser.LockOneForRead("OutputDirPath", ref lockedVariables);
Dts.VariableDispenser.LockOneForRead("OutputData", ref lockedVariables);
Dts.VariableDispenser.GetVariables(ref lockedVariables);
fileName = (string)lockedVariables["User::OutputDirPath"].Value;
if (fileName == null || fileName.Length == 0)
{
fileName = @"C:\ScriptTaskResults.log";
}else{
fileName = fileName.Replace(".log", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".log"));
// fileName format ex: \\ssis-server01\FTP\ScriptTaskOutput_20200429011136.log
}
fileData = (string)lockedVariables["User::OutputData"].Value;
if (fileData == null || fileData.Length == 0 || fileData.ToLower().Contains("system.object"))
{
fileData = "OutputData value is empty. Nothing to report.";
}
using (StreamWriter w = File.AppendText(fileName))
{
Log(fileData, w);
}
using (StreamReader r = File.OpenText(fileName))
{
DumpLog(r);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
errorOccurred = true; // possible network logging issue. try to save to event log.
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry(string.Concat("ssis pkg output log issue. ", "\r\n", ex.Message.ToString()), EventLogEntryType.Error, 101, 1);
}
}
finally
{
if (lockedVariables != null) lockedVariables.Unlock();
if (errorOccurred) // fire event to store in SQL DTS log
{
Dts.Events.FireError(18, "ssis pkg output log issue.", "An Exception was caught during logging write and output data flush. Check the server event application log for details.", "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
public static void Log(string logMessage, TextWriter w)
{
w.Write("\r\nLog Entry : ");
w.WriteLine($"{DateTime.Now.ToLongTimeString()} {DateTime.Now.ToLongDateString()}");
w.WriteLine(" :");
w.WriteLine($" :{logMessage}");
w.WriteLine("-------------------------------");
}
public static void DumpLog(StreamReader r)
{
string line;
while ((line = r.ReadLine()) != null)
{
Console.WriteLine(line);
}
}
Title: Base64 Encoding With Simple Salt (and decoding)
XPCMD_SHELL Move & Load Data from Directory
***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY *****IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID select distinct mm.CustomerID Into #tCustomerID from myDatabase..myTable (nolock) mm left join myDatabase.dbo.myOtherTable (nolock) mo on mm.refID = mo.id left join myDatabase.dbo.myOtherTableDetail (nolock) mod on mo.id = mod.myOtherTableid and mm.SearchValue = mod.SearchValue where mm.refID is not null and mm.quantity is not null and mod.SearchValue is nullIF Exists (Select 1 From #tCustomerID)BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body ='
'--SELECT @body exec msdb.dbo.sp_send_dbmail @profile_name = 'dbmailProfile' ,@recipients = 'usergroup@mydomain.com' ,@subject = 'My Valid Data Monitor' ,@body = @body ,@body_format = 'HTML'END
My Valid Data Monitor
| CustomerID |
|---|
The new season is a great reason to make and keep resolutions. Whether it’s eating right or cleaning out the garage, here are some tips for making and keeping resolutions.
There are so many good reasons to communicate with site visitors. Tell them about sales and new products or update them with tips and information.
Write about something you know. If you don’t know much about a specific topic that will interest your readers, invite an expert to write about it.
Turn Up! Not every microphone has the same base volume for transmitting your voice through your computer to whoever or whatever is on the other end of the exchange. Some microphones have a higher volume as compared to others, and some microphones have a volume that is so low that the person on the other […]
The post How to Turn Up Mic Volume in Windows 10 – Appuals.com appeared first on TekPreacher.
'+@UNCPathAndLogTable+'
--UNIQUE AND RANDOM TRANSACTION NAMES (FOR HIGH VOLUME TRANSACTIONS PER SECOND)DECLARE @randomString VARCHAR(255) = CONVERT(varchar(255), NEWID());SELECT @randomString = replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','') + RIGHT(@randomString,10);PRINT ''+ CONVERT(VARCHAR,LEN(@randomString)) + ' CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES'SELECT @randomStringBEGIN TRANSACTION @randomStringSELECT replace(replace(replace(convert(varchar, getdate(), 120),':',''),'-',''),' ','');SELECT CONVERT(VARCHAR,getdate(),112) + LEFT(REPLACE(CONVERT(VARCHAR,getdate(),114),':',''),6);SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR(33),SYSDATETIMEOFFSET(),126), 1, 8), '-', '') + SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126),'T',''),'.',''),':',''),9,DATALENGTH(CONVERT(VARCHAR(33), SYSDATETIMEOFFSET(), 126)))COMMIT TRANSACTION @randomStringOUTPUT-- 24 CHARS / OF MAXIMUM 32 CHARS FOR LENGTH OF TRANSACTION NAMES-- 202005141039592610925CAA <- THIS IS THE ONE-- 20200514103959-- 20200514103959-- 202005141039598866412-0500