January 8, 2020 at 10:58 am
Dear SSC,
I've been working on something here to replace a function which was called as part of a Script Task. That part actually flies now but there's more to that: Business Logic (which I moved from the Script Task to a Transformation Script Component) which seems to be processed RBAR ( which renders my work from before essentially useless because performance is back to not existing).
As I have no clue about C# I can't do much on that front except if it makes sense remove the lastRow bit if it would actually help, does it? I'd prefer if I could make that Transformation fly because then I don't have to argue about the logic behind with anyone.
However I assume batshit still can't fly upwards therefore I'm attempting to replace the logic by plain TSQL but I'm not entirely sure if it actually would work or not, can someone give me feedback please if my TSQL logic seems feasible or if I'm missing something somewhere? I've added the suggested replacement code inside to make things a bit more readable, any advice is really appreciated.
#region Help: Introduction to the Script Component
/* The Script Component allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services data flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script component. */
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
/// <summary>
/// This is the class to which to add your code. Do not change the name, attributes, or parent
/// of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
#region Help: Using Integration Services variables and parameters
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script component, according to whether or not your
* code needs to write into the variable. To do so, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and ReadWriteVariables properties in the
* Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable or parameter:
* DateTime startTime = Variables.MyStartTime;
*
* Example of writing to a variable:
* Variables.myStringVariable = "new value";
*/
#endregion
#region Help: Using Integration Services Connnection Managers
/* Some types of connection managers can be used in this script component. See the help topic
* "Working with Connection Managers Programatically" for details.
*
* To use a connection manager in this script, first ensure that the connection manager has
* been added to either the list of connection managers on the Connection Managers page of the
* script component editor. To add the connection manager, save this script, close this instance of
* Visual Studio, and add the Connection Manager to the list.
*
* If the component needs to hold a connection open while processing rows, override the
* AcquireConnections and ReleaseConnections methods.
*
* Example of using an ADO.Net connection manager to acquire a SqlConnection:
* object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
* SqlConnection salesDBConn = (SqlConnection)rawConnection;
*
* Example of using a File connection manager to acquire a file path:
* object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
* string filePath = (string)rawConnection;
*
* Example of releasing a connection manager:
* Connections.SalesDB.ReleaseConnection(rawConnection);
*/
#endregion
#region Help: Firing Integration Services Events
/* This script component can fire events.
*
* Example of firing an error event:
* ComponentMetaData.FireError(10, "Process Values", "Bad value", "", 0, out cancel);
*
* Example of firing an information event:
* ComponentMetaData.FireInformation(10, "Process Values", "Processing has started", "", 0, fireAgain);
*
* Example of firing a warning event:
* ComponentMetaData.FireWarning(10, "Process Values", "No rows were received", "", 0);
*/
#endregion
/// <summary>
/// This method is called once, before rows begin to be processed in the data flow.
///
/// You can remove this method if you don't need to do anything here.
/// </summary>
public override void PreExecute()
{
base.PreExecute();
/*
* Add your code here
*/
}
/// <summary>
/// This method is called after all the rows have passed through this component.
///
/// You can delete this method if you don't need to do anything here.
/// </summary>
public override void PostExecute()
{
base.PostExecute();
/*
* Add your code here
*/
}
/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
/// string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
/// Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
/*
* Add your code here
*/
// initialize variables (business logic)
bool VPrevLVS = false;
int VOSOXXXXX = 0;
int VOSOBTG4 = 0;
int VTOD = 0;
int VTOXXXXX = 0;
int VTSXXXXX = 0;
int VTSDealer = 0;
bool VDone = false;
bool VVAFDone = false;
DateTime VSDSXXXXX = DateTime.MinValue;
DateTime VSDSDealer = DateTime.MinValue;
DateTime VSDSDealer2 = DateTime.MinValue;
int VRDC = 0;
bool VFactoryInvoiceCheck = true;
int VTotalWholesale = 0;
int VTotalRetail = 0;
bool VFistWsBtgPassed = false;
// variables for flow control, statement generation
//String insertStatement = "";
bool lastRow = false;
while (!lastRow)
{
// Input Row Fields
Boolean LVS = Row.LVS;
Int32 LCS_ID = Row.LCSID;
Int32 Value = Convert.ToInt32(Row.Value);
Int32 O_D_BTG_ID = Row.ODBTGID;
Int16 PS_BTG_ID = Row.PSBTGID;
Int32 PS_LC_ID = Row.PSLCID;
Int16 BTG_ID = Row.BTGID;
DateTime E_D = Row.ED;
int RDGC = Row.RDGC;
//-------------------------------------------------------------
// InitializeRunningTotals
if (VPrevLVS == true)
VVAFDone = false;
VDone = false;
VFactoryInvoiceCheck = true;
VOSOXXXXX = 0;
VOSOBTG4 = 0;
VTOXXXXX = 0;
VTOD = 0;
VTSXXXXX = 0;
VTSDealer = 0;
VTotalWholesale = 0;
VTotalRetail = 0;
VSDSDealer2 = DateTime.MinValue;
VRDC = 0;
VFistWsBtgPassed = false;
// additional initializations
//VSDSXXXXX = DateTime.MinValue;
//VSDSDealer = DateTime.MinValue;
// I don't get this part, I mean if LVS = true then true else false but
// I don't see how to deal with WHEN LVS = True then VVAFDone = False
// would I need to add another column for VVAFDone and potentially VPrefLVS? It's used only for
// the first if condition to check
if (LVS == true)
{
VPrevLVS = true;
}
else
{
VPrevLVS = false;
}
//-------------------------------------------------------------
// VAFClearance
int VAFClearance;
if (LCS_ID == 12)
{
VVAFDone = true;
VAFClearance = 1;
}
else
{
VAFClearance = 0;
}
//[VAFClearance] = SELECT CASE WHEN LCS_ID = 12 then 1 else 0
//-------------------------------------------------------------
// Retail
int Retail;
if (LCS_ID == 7 || LCS_ID == 107)
{
Retail = Value;
}
else if (LCS_ID == 10)
{
Retail = -1;
}
else
{
Retail = 0;
}
//[Retail] = SELECT CASE WHEN LCS_ID = 7 OR LCS_ID = 107 then [Value]
//when LCS_ID = 10 then -1
//else 0
//-------------------------------------------------------------
// TotalRetail
int TotalRetail;
VTotalRetail = VTotalRetail + Retail;
TotalRetail = VTotalRetail;
//[TotalRetail] = SELECT 0 + Retail
//-------------------------------------------------------------
// Wholesale
int Wholesale;
if (LCS_ID == 3 || LCS_ID == 103)
{
Wholesale = Value;
}
else if (LCS_ID == 6)
{
Wholesale = -1;
}
else
{
Wholesale = 0;
}
//[Wholesale] = SELECT CASE WHEN LCS_ID = 3 or LCS_ID = 103 then [Value]
//WHEN LCS_ID = 6 then -1
//ELSE 0
//-------------------------------------------------------------
// TotalWholesale
int TotalWholesale;
VTotalWholesale = VTotalWholesale + Wholesale;
TotalWholesale = VTotalWholesale;
//[TotalWholesale] = SELECT 0 + [Wholesale]
//-------------------------------------------------------------
// SOXXXXX
int SOXXXXX;
if (LCS_ID == 23) SOXXXXX = Value;
else if (LCS_ID == 33) SOXXXXX = -Value;
else if ((LCS_ID == 1 || LCS_ID == 101) && O_D_BTG_ID == 3) SOXXXXX = Value;
else if (LCS_ID == 11 && PS_BTG_ID != 3 && O_D_BTG_ID == 3) SOXXXXX = 1;
else if (LCS_ID == 11 && PS_BTG_ID == 3 && O_D_BTG_ID != 3) SOXXXXX = -1;
else if (LCS_ID == 12 && O_D_BTG_ID == 3) SOXXXXX = -1;
else SOXXXXX = 0;
//[SOXXXXX] = SELECT CASE WHEN LCS_ID = 23 then [Value]
//WHEN LCS_ID = 33 then (-1 * [Value])
//WHEN ((LCS_ID = 1 OR LCS_ID = 101) AND O_D_BTG_ID = 3) then [Value]
//WHEN (LCS_ID = 11 AND PS_BTG_ID != 3 AND O_D_BTG_ID = 3) then 1
//WHEN (LCS_ID = 11 AND PS_BTG_ID = 3 AND O_D_BTG_ID != 3) then -1
//WHEN (LCS_ID = 12 AND O_D_BTG_ID = 3) then -1
//ELSE 0
//-------------------------------------------------------------
// SOBTG4
int SOBTG4;
if (LCS_ID == 24) SOBTG4 = Value;
else if (LCS_ID == 34) SOBTG4 = Value * -1;
else if ((LCS_ID == 1 || LCS_ID == 101) && O_D_BTG_ID == 4) SOBTG4 = Value;
else if ((LCS_ID == 7 || LCS_ID == 107) && PS_BTG_ID == 4 && (PS_LC_ID == 1 || PS_LC_ID == 2 || PS_LC_ID == 11)) SOBTG4 = -1;
else if (LCS_ID == 11 && PS_BTG_ID != 4 && O_D_BTG_ID == 4) SOBTG4 = 1;
else if (LCS_ID == 11 && PS_BTG_ID == 4 && O_D_BTG_ID != 4) SOBTG4 = -1;
else if (LCS_ID == 12 && O_D_BTG_ID == 4) SOBTG4 = -1;
else SOBTG4 = 0;
//[SOBTG4] = SELECT CASE WHEN LCS_ID = 24 then [Value]
//WHEN LCS_ID = 34 then [Value] * -1
//WHEN ((LCS_ID = 1 OR LCS_ID = 101) AND O_D_BTG_ID = 4) then [Value]
//WHEN ((LCS_ID = 7 OR LCS_ID = 107) AND PS_BTG_ID = 4 AND (PS_LC_ID = 1 OR PS_LC_ID = 2 OR PS_LC_ID = 1)) then -1
//WHEN (LCS_ID = 11 and PS_BTG_ID != 4 AND O_D_BTG_ID = 4) then 1
//WHEN (LCS_ID = 11 and PS_BTG_ID = 4 AND O_D_BTG_ID != 4) then -1
//WHEN (LCS_ID = 12 AND O_D_BTG_ID = 4) then -1
//else 0
//-------------------------------------------------------------
// OrderBTG1
int OrderBTG1;
if (LCS_ID == 21) OrderBTG1 = Value;
else if (LCS_ID == 31) OrderBTG1 = Value * -1;
else if ((LCS_ID == 1 || LCS_ID == 100) && O_D_BTG_ID == 1) OrderBTG1 = Value;
else if (LCS_ID == 11 && O_D_BTG_ID == 1 && PS_BTG_ID != 1) OrderBTG1 = 1;
else if (LCS_ID == 11 && PS_BTG_ID == 1 && O_D_BTG_ID != 1) OrderBTG1 = -1;
else if (LCS_ID == 12 && O_D_BTG_ID == 1) OrderBTG1 = -1;
else OrderBTG1 = 0;
//[OrderBTG1] = SELECT CASE WHEN LCS_ID = 21 then [Value]
//WHEN LCS_ID = 31 then ([Value] * -1)
//WHEN ((LCS_ID = 1 OR LCS_ID = 100) AND O_D_BTG_ID = 1) then [Value]
//WHEN (LCS_ID = 11 AND O_D_BTG_ID = 1 AND PS_BTG_ID != 1) then 1
//WHEN (LCS_ID = 11 AND O_D_BTG_ID != 1 and PS_BTG_ID = 1 ) then -1
//WHEN (LCS_ID = 12 AND O_D_BTG_ID = 1) then -1
//ELSE 0
//-------------------------------------------------------------
// OrderBTG2
int OrderBTG2;
if (LCS_ID == 22) OrderBTG2 = Value;
else if (LCS_ID == 32) OrderBTG2 = Value * -1;
else if ((LCS_ID == 1 || LCS_ID == 100) && O_D_BTG_ID == 2) OrderBTG2 = Value;
else if (LCS_ID == 11 && PS_BTG_ID != 2 && O_D_BTG_ID == 2) OrderBTG2 = 1;
else if (LCS_ID == 11 && PS_BTG_ID == 2 && O_D_BTG_ID != 2) OrderBTG2 = -1;
else if (LCS_ID == 12 && O_D_BTG_ID == 2) OrderBTG2 = -1;
else OrderBTG2 = 0;
//[OrderBTG2] = SELECT CASE WHEN LCS_ID = 22 THEN [Value]
//WHEN LCS_ID = 32 then ([Value] * -1)
//WHEN ((LCS_ID = 1 OR LCS_ID = 100) AND O_D_BTG_ID = 2) then [Value]
//WHEN (LCS_ID = 11 AND PS_BTG_ID != 2 AND O_D_BTG_ID = 2) then 1
//WHEN (LCS_ID = 11 AND PS_BTG_ID = 2 AND O_D_BTG_ID != 2) then -1
//WHEN (LCS_ID = 12 AND O_D_BTG_ID = 2) then -1
//ELSE 0
//-------------------------------------------------------------
// Done
bool Done;
if (VVAFDone)
{
VDone = true;
Done = true;
}
else if (TotalRetail > 0)
{
VDone = true;
Done = true;
}
else if (TotalWholesale <= 0 || TotalRetail <= 0)
{
VDone = false;
Done = false;
}
else if (TotalWholesale <= 0 && BTG_ID == 2)
{
VDone = false;
Done = false;
}
Done = VDone;
//[Done] = SELECT CASE WHEN [Done] = true then true
//WHEN TotalRetail > 0 then true
//WHEN TotalWholesale <= 0 OR TotalRetail <= 0 then false
//WHEN TotalWholeSale <= 0 AND BTG_ID = 2 then false
//-------------------------------------------------------------
// FactoryInvoice
int FactoryInvoice;
if (VDone) FactoryInvoice = 0;
else if (LCS_ID == 102 && O_D_BTG_ID != 4) FactoryInvoice = Value;
else if (LCS_ID == 2 && O_D_BTG_ID != 4 && VFactoryInvoiceCheck) FactoryInvoice = 1;
else FactoryInvoice = 0;
//[FactoryInvoice] = SELECT CASE When [Done] = true then 0
//WHEN LCS_ID = 102 AND O_D_BTG_ID != 4 then [Value]
//WHEN LCS_ID = 2 AND O_D_BTG_ID != AND FactoryInvoice = true then 1
//ELSE 0
//-------------------------------------------------------------
// StockEntryXXXXX
int StockEntryXXXXX;
if ((LCS_ID == 102 || LCS_ID == 6) && O_D_BTG_ID != 4)
{
StockEntryXXXXX = Value;
}
else if (LCS_ID == 2 && O_D_BTG_ID != 4 && VFactoryInvoiceCheck)
{
StockEntryXXXXX = Value;
}
else if (LCS_ID == 3 && O_D_BTG_ID != 4)
{
StockEntryXXXXX = -1;
}
else if (LCS_ID == 12 && PS_LC_ID == 2)
{
StockEntryXXXXX = -1;
}
else if (LCS_ID == 103 && O_D_BTG_ID != 4)
{
StockEntryXXXXX = Value * -1;
}
else
{
StockEntryXXXXX = 0;
}
//[StockEntryXXXXX] = SELECT CASE WHEN ((LCS_ID = 102 OR LCS_ID = 6) AND O_D_BTG_ID != 4) THEN [Value]
//WHEN (LCS_ID = 2 AND O_D_BTG_ID != 4 and FactoryInvoice = true) THEN [Value]
//WHEN LCS_ID = 3 AND O_D_BTG_ID != 4 THEN -1
//WHEN LCS_ID = 12 AND PS_LC_ID = 2 THEN -1
//WHEN LCS_ID = 103 and O_D_BTG_ID != 4 then ([Value] * -1)
//ELSE 0
//-------------------------------------------------------------
// StockEntryDealer
int StockEntryDealer;
if (LCS_ID == 3 && (O_D_BTG_ID == 1 || O_D_BTG_ID == 2)) StockEntryDealer = 1;
else if ((LCS_ID == 7 || LCS_ID == 6) && (O_D_BTG_ID == 1 || O_D_BTG_ID == 2)) StockEntryDealer = -1;
else if (LCS_ID == 10 && O_D_BTG_ID == 2) StockEntryDealer = 1;
else if (LCS_ID == 12 && PS_BTG_ID == 2) StockEntryDealer = -1;
else if (LCS_ID == 103 && (O_D_BTG_ID == 1 || O_D_BTG_ID == 2)) StockEntryDealer = Value;
else if (LCS_ID == 107 && (O_D_BTG_ID == 1 || O_D_BTG_ID == 2) && Value == -1) StockEntryDealer = 1;
else if (LCS_ID == 107 && (O_D_BTG_ID == 1 || O_D_BTG_ID == 2) && Value == 1) StockEntryDealer = -1;
else StockEntryDealer = 0;
//[StockEntryDealer] = SELECT CASE WHEN (LCS_ID = 3 AND (O_D_BTG_ID = 1 OR O_D_BTG_ID = 2)) then 1
//WHEN ((LCS_ID = 7 OR LCS_ID = 6) AND (O_D_BTG_ID = 1 OR O_D_BTG_ID = 2)) then -1
//WHEN LCS_ID = 10 AND O_D_BTG_ID = 2 then 1
//WHEN LCS_ID = 12 AND PS_BTG_ID = 2 then -1
//WHEN (LCS_ID = 103 AND (O_D_BTG_ID = 1 OR O_D_BTG_ID = 2)) then [Value]
//WHEN (LCS_ID = 107 AND (O_D_BTG_ID = 1 OR O_D_BTG_ID = 2) AND [Value] = -1) THEN 1
//WHEN (LCS_ID = 107 AND (O_D_BTG_ID = 1 OR O_D_BTG_ID = 2) AND [Value] = 1) THEN -1
//ELSE 0
//-------------------------------------------------------------
// WarrantyNotificationCard
int WarrantyNotificationCard;
if (LCS_ID == 9 || LCS_ID == 109)
{
WarrantyNotificationCard = Value;
}
else
{
WarrantyNotificationCard = 0;
}
//[WarrantyNotificationCard] = SELECT CASE WHEN LCS_ID = 9 OR LCS_ID = 109 THEN [Value]
//ELSE 0
//-------------------------------------------------------------
// FR
int FR;
if (LCS_ID == 8 || LCS_ID == 108)
{
FR = Value;
}
else
{
FR = 0;
}
//[FR] = SELECT CASE WHEN LCS_ID = 8 OR LCS_ID = 108
//then [Value]
//ELSE 0
//-------------------------------------------------------------
// FRAstra
int FRAstra;
if (LCS_ID == 13)
{
FRAstra = Value;
}
else
{
FRAstra = 0;
}
//[FRAstra] = SELECT CASE WHEN LCS_ID = 13 then [Value]
//ELSE 0
//-------------------------------------------------------------
// OSOXXXXX
int OSOXXXXX;
if (VDone || (VAFClearance > 0))
{
VOSOXXXXX = 0;
}
else
{
VOSOXXXXX = VOSOXXXXX + SOXXXXX;
}
OSOXXXXX = VOSOXXXXX;
//[OSOXXXXX] = SELECT CASE WHEN [Done] > 0 OR VAFClearance > 0
//THEN 0
//ELSE OSOXXXXX + SOXXXXX
//-------------------------------------------------------------
// OSOBTG4
int OSOBTG4;
if (VDone || (VAFClearance > 0))
{
VOSOBTG4 = 0;
}
else
{
VOSOBTG4 = VOSOBTG4 + SOBTG4;
}
OSOBTG4 = VOSOBTG4;
//[OSOBTG4] = SELECT CASE WHEN [Done] > 0 OR VAFClearance > 0 then 0
//else OSOBTG4 + SOBTG4
//-------------------------------------------------------------
// TOXXXXXX
int TOXXXXXX;
if (VAFClearance > 0)
{
VTOXXXXX = 0;
}
else if (O_D_BTG_ID == 2)
{
VTOXXXXX = VTOXXXXX + OrderBTG2 - Wholesale;
}
else
{
VTOXXXXX = VTOXXXXX + OrderBTG2;
}
TOXXXXXX = VTOXXXXX;
//[TOXXXXXX] = SELECT CASE WHEN VAFClearance > 0 then 0
//WHEN O_D_BTG_ID = 2 then (TOXXXXXX + OrderBTG2 - Wholesale)
//ELSE TOXXXXXX + OrderBTG2
//-------------------------------------------------------------
// TOD
int TOD;
if (VAFClearance > 0)
{
VTOD = 0;
}
else if (O_D_BTG_ID == 1)
{
VTOD = VTOD + OrderBTG1 - Wholesale;
}
else
{
VTOD = VTOD + OrderBTG1;
}
TOD = VTOD;
//[TOD] = SELECT CASE WHEN VAFClearance > 0 then 0
//WHEN O_D_BTG_ID = 1 THEN ([TOD] + OrderBTG1 - Wholesale)
//ELSE [TOD] + OrderBTG1
//-------------------------------------------------------------
// TotalStockXXXXX
int TotalStockXXXXX;
if (VAFClearance > 0)
{
VTSXXXXX = 0;
}
else
{
VTSXXXXX = VTSXXXXX + StockEntryXXXXX;
}
TotalStockXXXXX = VTSXXXXX;
//[TotalStockXXXXX] = SELECT CASE WHEN VAFClearance > 0 then 0
//ELSE (TotalStockXXXXX + StockEntryXXXXX)
//-------------------------------------------------------------
// TotalStockDealer
int TotalStockDealer;
if (VAFClearance > 0)
{
VTSDealer = 0;
}
else
{
VTSDealer = VTSDealer + StockEntryDealer;
}
TotalStockDealer = VTSDealer;
//[TotalStockDealer] = SELECT CASE WHEN VAFClearance > 0 then 0
//ELSE ([TotalStockDealer] + [StockEntryDealer])
//-------------------------------------------------------------
// StartDateStockXXXXX
DateTime StartDateStockXXXXX;
if ((LCS_ID == 2 || LCS_ID == 102) && BTG_ID != 4)
{
VSDSXXXXX = E_D;
StartDateStockXXXXX = E_D;
}
else if ((LCS_ID == 3 || LCS_ID == 103) && BTG_ID != 4)
{
VSDSXXXXX = DateTime.MinValue;
StartDateStockXXXXX = DateTime.MinValue;
}
else if ((LCS_ID == 6 || LCS_ID == 106) && BTG_ID != 4)
{
VSDSXXXXX = E_D;
StartDateStockXXXXX = E_D;
}
else StartDateStockXXXXX = VSDSXXXXX;
//[StartDateStockXXXXX] = SELECT CASE WHEN ((LCS_ID = 2 OR LCS_ID = 102) AND BTG_ID != 4) THEN E_D
//WHEN ((LCS_ID = 3 OR LCS_ID = 103) AND BTG_ID != 4) THEN NULL
//WHEN ((LCS_ID = 6 OR LCS_ID = 106) AND BTG_ID != 4) THEN E_D
//ELSE NULL
//-------------------------------------------------------------
// SDSDealer
DateTime SDSDealer;
if ((LCS_ID == 3 || LCS_ID == 103) && BTG_ID != 4)
{
VSDSDealer = E_D;
SDSDealer = E_D;
}
else if ((LCS_ID == 6 || LCS_ID == 106) && BTG_ID != 4)
{
VSDSDealer = DateTime.MinValue;
SDSDealer = DateTime.MinValue;
}
SDSDealer = VSDSDealer;
//[SDSDealer] = SELECT CASE WHEN ((LCS_ID = 3 OR LCS_ID = 103) AND BTG_ID != 4) THEN E_D
//WHEN ((LCS_ID = 6 OR LCS_ID = 106) AND BTG_ID != 4) THEN NULL
//-------------------------------------------------------------
// SDSDealer2
DateTime SDSDealer2;
VRDC = VRDC + RDGC;
if ((LCS_ID == 3 || LCS_ID == 103) && BTG_ID != 4)
{
if (VSDSDealer2.Equals(DateTime.MinValue))
{
VSDSDealer2 = E_D;
VRDC = 0;
SDSDealer2 = E_D;
}
else if (VRDC > 0)
{
VSDSDealer2 = E_D;
SDSDealer2 = E_D;
}
else SDSDealer2 = VSDSDealer2;
}
else if ((LCS_ID == 6 || LCS_ID == 106) && BTG_ID != 4)
{
VRDC = 0;
SDSDealer2 = DateTime.MinValue;
}
else SDSDealer2 = VSDSDealer2;
//[SDSDealer2] = SELECT CASE WHEN (((LCS_ID = 3 OR LCS_ID = 103) AND BTG_ID != 4) and SDSDealer2 = NULL) THEN E_D
//WHEN ((LCS_ID = 3 OR LCS_ID = 103) AND BTG_ID != 4) and RDGC > 0 THEN E_D
//WHEN (LCS_ID = 6 OR LCS_ID = 106) AND BTG_ID != 4) then NULL
//ELSE [SDSDealer2]
//[RDGC] = SELECT CASE WHEN (((LCS_ID = 3 OR LCS_ID = 103) AND BTG_ID != 4) and SDSDealer2 = NULL) THEN 0
//WHEN ((LCS_ID = 6 OR LCS_ID = 106) AND BTG_ID != 4) THEN 0
//ELSE RDGC
//-------------------------------------------------------------
// UpdateFactoryInvoiceCheck
if (LCS_ID == 2 && O_D_BTG_ID != 4 && VFactoryInvoiceCheck)
{
VFactoryInvoiceCheck = false;
}
// what? Isn't that done above already?
//-------------------------------------------------------------
// FirstWsBtg1
int FirstWsBtg1;
if ((LCS_ID == 3 || LCS_ID == 103) && O_D_BTG_ID == 1 && VFistWsBtgPassed == false)
{
VFistWsBtgPassed = true;
FirstWsBtg1 = 1;
}
else FirstWsBtg1 = 0;
//[FirstWsBtg1] = SELECT CASE WHEN ((LCS_ID = 3 OR LCS_ID = 103) AND O_D_BTG_ID = 1) then 1
//ELSE 0
}
}
}
January 9, 2020 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 9, 2020 at 4:40 pm
Doing a quick read over the code, it looks like the SQL you commented in should do similar things as the C# is. The only thing is that if you are looking to move this over from C# to TSQL, you probably want to remove anything that would make it a row-by-row operation.
The C# is also a bit weird in there. First, the function Input0_ProcessInputRow() takes a single row in and only operates on that single row. BUT it gets called for each row in Input0. So if you had 100 rows coming in, that function will get called 100 times. That is all good and fine, but then you have a while loop in there checking if the value of lastRow is false. The value of lastRow is set to False and never changed in the rest of the code. Therefore, that while loop to me looks like it will be an infinite loop (other experts correct me if I am wrong).
Towards the end, you had a comment "// what? Isn't that done above already?" and from my reading of the code, it is not if I am reading the code correctly. You are checking the value of it and you use it to set FactoryInvoice and StockEntryXXXXX.
I see various places where you could do a tiny bit of optimization as you have some "Else If"'s that are returning the same value as a different if/else if, but the performance improvement there will be hardly noticeable unless you had a lot of rows to work with.
My opinion, I would look at re-evaluating the logic to make sure it is doing what the end user is expecting. From my reading of that code and my understanding of C#, it is going to be sitting in an infinite loop. And even if it doesn't sit in an infinite loop, it is going to be calling that function one time per row. So that loop will run for each row from the input table which will make that an incredibly slow process.
Another thing I notice is that you have a lot of variables defined in the C# but you never pass those back anywhere. You grab a bunch of values from the row, store them in local variables, create a bunch of other local variables, set their values and never return them back to SSIS. My understanding of that C# script for SSIS is that it is essentially doing nothing.
The TL;DR version - I don't think that C# script does anything except get stuck in an infinite loop while reading the first row. Removing the while loop will make it run faster for sure as it is no longer an infinite loop, but the script is not doing anything.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 9, 2020 at 7:55 pm
what are you trying to do now? the previous script you had was inserting into a SQL table - I gave you the base script for that.
This one seem to have been setup as a transform or a destination component.
Have a look at these links
brief explanation
link 1 - we create new rows and add them to the pipeline output buffer
link 2 - we change the input buffer row - e.g. we change the values of the columns as needed (we can do more but this is short version)
link 3 - we process the input buffer row and do something with it - write to a file or to another database - but we code it ourselves and we can transform the data at this stage
the previous code I gave you was a Source Transformation
Note that on link 2 and 3 we don't do the type of loop you have - we just process each input row as we are given and "end of rows" is managed by SSIS itself
https://www.tutorialgateway.org/ssis-script-component-as-source/
https://www.tutorialgateway.org/ssis-script-component-as-transformation/
https://www.tutorialgateway.org/ssis-script-component-as-destination/
On a different note - RBAR in C# is not the same as in SQL - in C# it works quite well as opposed to SQL.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply