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";
}
}
}
