/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace ST_de7f2bb135e34249a971e5d7c34fb4fa.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
// TODO: Add your code here
string today_FT = string.Empty, getBdate = string.Empty;
#region 1. // FT Output //
string FT_query =
"EXEC [CentralDataStoreSDL].[Daily].[SpUFAFinancialTransactionOutput_ScenarioData_REG] 'IN1016'";
string FT_connectionSql = @"Server=PSDC-USQLCDS001.za.sbicdirectory.com\CDSDEV,50000;Database=CentralDataStoreSDL;Integrated Security=true";
#region businessdate
//set file name with respect to businessdate
getBdate = "SELECT convert(varchar, MAX(CDSBusinessDate), 112) as BDate FROM Daily.UFAFinancialTransaction_ScenarioData_REG WHERE CDSStatusFlag = 1 AND SourceSystemID = 'IN1016'";
using (SqlConnection FT_connection = new SqlConnection(FT_connectionSql))
{
SqlCommand FT_command1 = new SqlCommand(getBdate, FT_connection);
FT_command1.CommandTimeout = 120;
FT_connection.Open();
SqlDataReader FT_reader1 = FT_command1.ExecuteReader();
try
{
while (FT_reader1.Read())
{
today_FT = FT_reader1["BDate"].ToString();
}
}
catch (Exception ex_DT)
{
MessageBox.Show(ex_DT.ToString());
}
}
#endregion
//File process
string file_loc = @"D:\CDSOutput\UFA\IN1016\RegressionTest\FT_Data\BA_IN1016_FT_" + today_FT + ".TXT";
StreamWriter FT_myFile = new StreamWriter(file_loc);
using (SqlConnection FT_connection = new SqlConnection(FT_connectionSql))
{
SqlCommand FT_command = new SqlCommand(FT_query, FT_connection);
FT_command.CommandTimeout = 120;
FT_connection.Open();
SqlDataReader FT_reader = FT_command.ExecuteReader();
try
{
while (FT_reader.Read())
{
FT_myFile.WriteLine(String.Format("{0}",
FT_reader["STRINGColumn"]));
}
}
catch (Exception ex_FT)
{
MessageBox.Show(ex_FT.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
FT_reader.Close();
FT_myFile.Close();
}
}
#endregion
#region 2. // BusinessTransactionOutput //
string query =
"EXEC [CentralDataStoreSDL].[Daily].[SpUFABusinessTransactionOutput_ScenarioData_REG] 'IN1016'";
string connectionSql = @"Server=PSDC-USQLCDS001.za.sbicdirectory.com\CDSDEV,50000;Database=CentralDataStoreSDL;Integrated Security=true";
//set file name with respect to businessdate
//var today = DateTime.Today.AddDays(-1).ToString("yyyyMMdd");
string file_loc1 = @"D:\CDSOutput\UFA\IN1016\RegressionTest\FT_Data\BA_IN1016_BT_" + today_FT + ".TXT";
StreamWriter myFile = new StreamWriter(file_loc1);
//StreamWriter myFile = new StreamWriter(@"C:\standardBank\General\test\ssis\BA_IN1016_BT_" + "20200508" + ".TXT");
using (SqlConnection connection = new SqlConnection(connectionSql))
{
SqlCommand command = new SqlCommand(query, connection);
command.CommandTimeout = 120;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
myFile.WriteLine(String.Format("{0}",
reader["STRINGColumn"]));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
reader.Close();
myFile.Close();
}
}
#endregion
#region 3. // val Output //
string query_val =
"EXEC [CentralDataStoreSDL].[Daily].[SpUFAValuationAndAccrualOutput_ScenarioData_REG] 'IN1016'";
string connectionSql_val = @"Server=PSDC-USQLCDS001.za.sbicdirectory.com\CDSDEV,50000;Database=CentralDataStoreSDL;Integrated Security=true";
//set file name with respect to businessdate
// 2. BA_IN1016_BT_YYYYMMDD.TXT
//var today_val = DateTime.Today.AddDays(-1).ToString("yyyyMMdd");
string file_loc2 = @"D:\CDSOutput\UFA\IN1016\RegressionTest\FT_Data\BA_IN1016_VAL_" + today_FT + ".TXT";
StreamWriter myFile_val = new StreamWriter(file_loc2);
using (SqlConnection connection_val = new SqlConnection(connectionSql_val))
{
SqlCommand command_val = new SqlCommand(query_val, connection_val);
command_val.CommandTimeout = 120;
connection_val.Open();
SqlDataReader reader_Val = command_val.ExecuteReader();
try
{
while (reader_Val.Read())
{
myFile_val.WriteLine(String.Format("{0}",
reader_Val["STRINGColumn"]));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
reader_Val.Close();
myFile_val.Close();
}
}
#endregion
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
No comments:
Post a Comment