SSIS ScriptTask Main_Script_Executable.cs
E.DIN Editor • November 17, 2021
Purpose: SSIS Package Main Script Executable with DB Connectivity Verification and Error logging
#region Namespaces
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.VisualBasic.FileIO;
using System.IO;
using System.Diagnostics;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading;
#endregion
namespace CP_AutoTerminations
{
/// ScriptMain entry point class.
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
private DataTable _dErrorsTable;
private string _messageData;
private string _fileNameOut;
//private ConnectionManager _connectMgr;
public DataTable ErrorsTable
{
get {
if (_dErrorsTable == null) _dErrorsTable = new DataTable();
if (_dErrorsTable.Columns.Count == 0) _dErrorsTable.Columns.Add(new DataColumn("Errors"));
return _dErrorsTable; }
set => _dErrorsTable = value;
}
public string MessageData
{
get { return _messageData ?? "" ; }
set => _messageData = value;
}
public string FileNameOut
{
get { return _fileNameOut; }
set => _fileNameOut = value;
}
/// <summary>
/// This method is called when this script task executes in the control flow.
/// and sets the value of Dts.TaskResult to indicate success or failure.
/// </summary>
public void Main()
{
DataTable csvData = new DataTable();
StringBuilder sb = new StringBuilder();
try
{
//SetLogFile(false);
//string fileNameOut = this.FileNameOut;
StringWriter sWriter;
string sQuery;
string sQuerySuffix;
int rsNumber = 0;
this.MessageData = string.Empty;
string fileNameIn = (string)ReadVariable("User::InputDirPath").ToString();
//string fileNameEx = (string)ReadVariable("User::OutputExPath").ToString();
csvData = GetDataTableFromCsvFile(fileNameIn);
if (csvData == null || Dts.TaskResult == 1 || ErrorsTable.Rows?.Count > 0)
{
//if error occurred, InputDirPath has changed, reset it here, before the return;
SetLogFile(true);
if (ErrorsTable.Rows.Count > 0)
{
foreach (DataRow row in ErrorsTable.Rows)
{
this.MessageData += " - " + row.ItemArray[0].ToString();
}
WriteVariable("User::OutputData", this.MessageData);
}
return; // EXIT EXIT EXIT
}
// debug //
//using (StreamWriter w = File.AppendText(this.FileNameOut))
//{
// var msg = $"Acquiring Db Connection: " + Dts.Connections["DatabaseConnection"].ConnectionString;
// LogEntry(msg, w);
//}
ConnectionManager cm;
OleDbConnection sqlConn;
cm = Dts.Connections["DatabaseConnection"];
sqlConn = (OleDbConnection)cm.AcquireConnection(Dts.Transaction);
//sqlConn.ConnectionTimeout = 0;
Thread.Sleep(1000);
//string fileXmlOut = fileNameOut;
// debug //
//if (sqlConn != null)
//{
// using (StreamWriter w = File.AppendText(this.FileNameOut))
// {
// var msg = "Acquired Db Connection.";
// LogEntry(msg, w);
// }
//}
//Read from Datatable and process each row by Execute sproc
foreach (DataRow cvrRow in csvData.Rows)
{
// RESET FILE NAME, each
//this.FileNameOut = fileNameOut;
try
{
if ((string) cvrRow.ItemArray[0].ToString().ToLower() == "shortname") continue;
sQuerySuffix = "@name='[0]', @Acc='[1]', @NPI='[2]', @TermAll=0";
sQuerySuffix = sQuerySuffix.Replace("[0]", (string) cvrRow.ItemArray[0]);
sQuerySuffix = sQuerySuffix.Replace("[1]", (string) cvrRow.ItemArray[1]);
sQuerySuffix = sQuerySuffix.Replace("[2]", (string) cvrRow.ItemArray[2]);
//sQuerySuffix = sQuerySuffix.Replace("[3]", cvrRow.ItemArray[3].ToString().Trim());
sQuerySuffix = sQuerySuffix.Replace("'NULL'", "NULL");
// example [Invoice_Process].dbo.[spInvManualRunTerm] @name=N'UAMS1',@Acc=NULL,@NPI=NULL, @TermAll=0;
sQuery = string.Concat(@"EXECUTE [InvoiceDB].dbo.[spInvManualRunTerm] ", sQuerySuffix, ";");
//MessageBox.Show(query);
// debug
//using (StreamWriter w = File.AppendText(this.FileNameOut))
//{
// LogEntry(sQuery, w);
//}
var dResultTable = (new DataSet("AutoTermsResult")).Tables.Add("Logging");
sWriter = new StringWriter();
++rsNumber;
using (var cmd = new OleDbCommand(sQuery, sqlConn))
{
Thread.Sleep(1000);
using (var da = new OleDbDataAdapter(cmd))
{
da.Fill(dResultTable);
dResultTable.WriteXml(sWriter);
Thread.Sleep(1000);
}
}
if (dResultTable?.Rows?.Count > 0)
{
sb = new StringBuilder();
sb.AppendLine(string.Join(",", cvrRow.ItemArray));
this.MessageData = string.Concat(@"Term Results for:" , sb.ToString(), Environment.NewLine , sWriter.ToString(), Environment.NewLine);
if (this.MessageData.Contains("Exception") || this.MessageData.Contains("Errors"))
{
--rsNumber;
this.ErrorsTable.Rows.Add(new object[] { this.MessageData });
}
else // write good xml output
{
using (StreamWriter w = File.AppendText(this.FileNameOut))
{
LogEntry(this.MessageData, w);
}
}
}
}
catch (Exception ex)
{
--rsNumber;
this.ErrorsTable.Rows.Add(new object[] { ex.Message });
this.LogResults(ex.Message, true);
//<< CHANGES THE LOGFILE DIRECTORY to /EXCEPTION
}
}
WriteVariable("User::OutputData", this.MessageData);
sqlConn?.Close();
Dts.ExecutionValue = rsNumber;
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
//catch possible null EX, it has happened.
if (ex.Equals(null)) ex = new Exception("InvManualTerm Package.Main. Unknown exception in script task main function.");
WriteVariable("User::OutputData", ex.Message);
ThrowAndFail(ex.Message);
}
finally
{
if (Dts.TaskResult == 1 || ErrorsTable.Rows?.Count > 0)
{
if (ErrorsTable.Rows?.Count > 0)
{
//RESET MessageData
this.MessageData = "Parameters: ";
if (csvData?.Rows.Count > 0)
{
foreach (DataRow dr in csvData.Rows)
{
sb.AppendLine(string.Join(",", dr.ItemArray));
}
}
else
{
sb.AppendLine("null");
}
foreach (DataRow dr in ErrorsTable?.Rows)
{
sb.AppendLine(dr.ItemArray[0].ToString());
}
this.MessageData += sb.ToString();
WriteVariable("User::OutputData", this.MessageData);
}
this.LogResults(this.MessageData, true);
//<< CHANGES THE LOGFILE DIRECTORY to /EXCEPTION
SetParmsDestination(true);
//Dts.TaskResult = (int) ScriptResults.Failure;
}
else
{
SetParmsDestination(false);
//Dts.TaskResult = (int) ScriptResults.Success;
}
}
Dts.TaskResult = (int) ScriptResults.Success;
//let it succeed either way and send email notification
}
public void SetLogFile(bool isError)
{
bool bFixedName = false;
string sDir;
this.FileNameOut = ReadVariable("User::OutputDirPath").ToString();
if (string.IsNullOrEmpty(this.FileNameOut))
{
bFixedName = true;
this.FileNameOut = string.Concat(@"\\server01\Backup\Terms\InvTermsParameters_", DateTime.Now.ToString("yyyyMMddHHmm"), ".log");
sDir = FileNameOut.Substring(0,FileNameOut.LastIndexOf('\\'));
//sDir = Path.GetDirectoryName(this.FileNameOut); //this.FileNameOut.Substring(0,this.FileNameOut.LastIndexOf(@"\"))
if (!string.IsNullOrEmpty(sDir) && !Directory.Exists(sDir))
{
try
{
Directory.CreateDirectory(sDir);
}
catch(Exception ex)
{
bFixedName = false;
this.MessageData += Environment.NewLine + ex.ToString();
}
}
}
if (!this.FileNameOut.Contains(DateTime.Now.ToString("yyyy")))
{
bFixedName = true;
this.FileNameOut = this.FileNameOut.Replace(".log", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmm"), ".log"));
// fileNameOut format ex: \\Server01\Backup\Terms\InvTermsOutput_2020042901.log
}
if (isError.Equals(true))
{
bFixedName = true;
sDir = this.FileNameOut.Replace(@"\BACKUP\", @"\EXCEPTIONS\");
sDir = sDir.Substring(0,sDir.LastIndexOf('\\'));
if (!string.IsNullOrEmpty(sDir) && !Directory.Exists(sDir))
{
try
{
Directory.CreateDirectory(sDir);
}
catch(Exception ex)
{
this.MessageData += Environment.NewLine + ex.ToString();
}
}
if (!string.IsNullOrEmpty(sDir) && Directory.Exists(sDir))
{
this.FileNameOut = this.FileNameOut.Replace(@"\BACKUP\", @"\EXCEPTIONS\");
} //else don't change it, logs will dump in the BACKUP folder
WriteVariable("User::OutputExPath", this.FileNameOut);
//this.FileNameOut = ReadVariable("User::OutputExPath").ToString();
// fileNameOut format ex: \\Server01\Incoming\Terms\InvTermsOutput_2020042901.log
// EX. UNC: \\Server01\EXCEPTIONS\Terms\InvTermsOutput_2020042901.log
using (StreamWriter w = File.AppendText(this.FileNameOut))
{
LogEntry("SetLogFile.isError=True", w);
}
}
if (bFixedName.Equals(true))
{
WriteVariable("User::OutputDirPath", this.FileNameOut);
}
}
//Example: object myVarValue = "Abcd"; WriteVariable("User::MyVar", myVarValue);
private void SetParmsDestination(bool IsError)
{
if (string.IsNullOrEmpty(this.FileNameOut))
{
SetLogFile(IsError);
}
// get base input file variable, and move it, if previous attempt has failed
string fileParmsIncoming = (string)ReadVariable("$Package::InputFilePath").ToString();
if (File.Exists(fileParmsIncoming)) //then move it
{
//prepare destination
string fileDestination = this.FileNameOut.Replace(".log", ".csv");
if (IsError.Equals(true))
{
fileDestination = fileDestination.Replace(@"\BACKUP\", @"\EXCEPTIONS\");
}
if (File.Exists(fileDestination)) //then rename it
{
fileDestination = fileDestination.Replace(".csv", string.Concat("_", DateTime.Now.ToString("yyyyMMddHHmm"), ".csv"));
}
File.Move(fileParmsIncoming, fileDestination);
Thread.Sleep(1000);
}
}
private void WriteVariable(string varName, object varValue)
{
try
{
Variables vars = null;
Dts.VariableDispenser.LockForWrite(varName);
Dts.VariableDispenser.GetVariables(ref vars);
try
{
vars[varName].Value = varValue;
}
finally
{
vars.Unlock();
}
}
catch (Exception ex)
{
throw;
}
}
//Example: object myVarValue = ReadVariable("User::MyVar");
private object ReadVariable(string varName)
{
var result = new object();
try
{
result = null;
Variables vars = null;
Dts.VariableDispenser.LockForRead(varName);
Dts.VariableDispenser.GetVariables(ref vars);
try
{
result = vars[varName].Value;
}
finally
{
vars.Unlock();
}
}
catch (Exception)
{
throw;
}
return result;
}
private DataTable GetDataTableFromCsvFile(string csvFilePath)
{
string sError;
DataTable csvData = (new DataSet("AutoTermsResult")).Tables.Add("Logging");
try
{
using (TextFieldParser csvReader = new TextFieldParser(csvFilePath))
{
csvReader.SetDelimiters(new string[] {","});
csvReader.HasFieldsEnclosedInQuotes = false;
string[] colFields = csvReader.ReadFields();
if (colFields != null)
{
foreach (var column in colFields)
{
DataColumn dc = new DataColumn(column);
dc.DefaultValue = "";
dc.AllowDBNull = false;
csvData.Columns.Add(dc);
}
while (!csvReader.EndOfData)
{
string[] fieldData = csvReader.ReadFields();
if (fieldData == null) continue;
//Making empty value as null
int j = 0;
int i = 0;
for (i = 0; i < fieldData.Length; i++)
{
var s = fieldData[i].Trim();
if (string.IsNullOrEmpty(s))
{
s = "NULL";
j++;
}
else
{
s= fieldData[i].Trim();
}
fieldData[i] = s;
}
csvData.Rows.Add(fieldData);
// VALIDATION: NO VALUES, ALL NULL VALUES, MISSING FIELDS
if (i==0 || j > 0 || fieldData.Length < 3)
{
sError = "CSV input file failure. DATA VALUES VALIDATION. Data values are required for name,Acc,NPI,TermAll.";
ThrowAndFail(sError);
return csvData;
}
}
}
}
// COLUMN COUNT VALIDATION
if (csvData.Columns?.Count < 4)
{
sError = "CSV input file failure. COLUMN COUNT VALIDATION. Header columns required:
name,Acc,NPI,TermAll
.";
ThrowAndFail(sError);
}
// COLUMN 1 VALIDATION
if (csvData.Columns[0].ColumnName != "name")
{
sError = "CSV input file failure. COLUMN 1 VALIDATION. Column1 is Name.";
ThrowAndFail(sError);
}
// COLUMN 2 VALIDATION
if (csvData.Columns[1].ColumnName != "Acc")
{
sError = "CSV input file failure. COLUMN 2 VALIDATION. Column2 is Acc.";
ThrowAndFail(sError);
}
//COLUMN 3 VALIDATION
if (csvData.Columns[2].ColumnName != "NPI")
{
sError = "CSV input file failure. COLUMN 3 VALIDATION. Column3 is NPI.";
ThrowAndFail(sError);
}
//COLUMN 4 VALIDATION
if (csvData.Columns[3].ColumnName != "TermAll")
{
sError = "CSV input file failure. COLUMN 4 VALIDATION. Column4 is TermAll.";
ThrowAndFail(sError);
}
// ROW COUNT VALIDATION
if (csvData.Rows.Equals(null) || csvData.Rows.Count < 1)
{
sError = "CSV input file failure. ROW COUNT VALIDATION. Column Header row and at least 1 data row are required.";
ThrowAndFail(sError);
}
//StringWriter sWriter = new StringWriter();
//csvData.WriteXml(sWriter);
//using (StreamWriter w = File.AppendText(this.FileNameOut))
//{
// LogEntry(sWriter.ToString(), w);
//}
}
catch (Exception ex)
{
WriteVariable("User::OutputData", ex.Message);
ThrowAndFail(ex.Message);
//Dts.TaskResult = (int)ScriptResults.Failure;
//throw ex;
}
return csvData;
}
private void ThrowAndFail(string message)
{
this.ErrorsTable.Rows.Add(new object[] { message });
Dts.Events.FireError(0, "InvManualRunTerm Package.Error", message + Environment.NewLine, string.Empty, 0);
//Dts.TaskResult = (int)ScriptResults.Failure;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides shorthand class for setting the result of the script.
/// </summary>
private enum ScriptResults
{
Success = DTSExecResult.Success,
Failure = DTSExecResult.Failure
};
#endregion
public void LogResults(string fileData, bool isError)
{
//<?xml version = "1.0" encoding = "UTF-8"?>
bool errorOccurred = false; //in event all logging methods fail
try
{
SetLogFile(isError);
if (String.IsNullOrEmpty(fileData) || fileData.ToLower().Contains("system.object"))
{
fileData = "OutputData value is empty. Nothing to log.";
WriteVariable("User::OutputData", fileData);
}
using (StreamWriter w = File.AppendText(this.FileNameOut))
{
LogEntry(fileData, w);
}
//using (StreamReader r = File.OpenText(this.FileNameOut))
//{
//
DumpLogToConsole(r);
//}
}
catch (Exception ex)
{
errorOccurred = true; // possible network logging issue. try to save to event log.
ThrowAndFail(ex.Message);
WriteVariable("User::OutputData", ex.Message);
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry(string.Concat("InvManualTerm Package.Error. ", Environment.NewLine, ex.Message.ToString()), EventLogEntryType.Error, 101, 1);
}
//Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
if (errorOccurred) // fire event to store in SQL DTS log
{
Dts.Events.FireError(18, "InvManualTerm Package.Error", "An Exception was caught during logging write and output data flush. Check server event application log for details.", "", 0);
//Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
public static void LogEntry(string logMessage, TextWriter w)
{
w.Write(Environment.NewLine + "ScriptTaskMain: " + $"{DateTime.Now:yyyyMMdd} : {DateTime.Now.ToLongTimeString()}");
w.WriteLine($" :{logMessage}");
}
public static void DumpLogToConsole(StreamReader r)
{
string line;
while ((line = r.ReadLine()) != null)
{
Console.WriteLine(line);
}
}
}
public partial class CheckConnection
{
private ConnectionManager connectionManager;
//"OLEDB", "ADO.NET:SQL", "ADO.NET:OLEDB"
public void CreateOLEConnection(Package p, string csType)
{
connectionManager = p.Connections.Add(csType);
if (csType == "")
//Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=myDatabase;Data Source=Server01
{ //Server=MYSERVER;Provider=SQLOLEDB.1;Pwd= xxxxxxx;User ID= xxxxx;Initial Catalog=mySource;OLE DB Services=-2"
connectionManager.ConnectionString = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=myDatabase;Data Source=Server01;";
}
if (csType == "OLEDB")
{
connectionManager.ConnectionString = @"Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabase;Data Source=Server01;Auto Translate=False;";
}
connectionManager.Name = "SSIS Connection Manager";
connectionManager.Description = "DB connection";
}
}
}
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