Hi SSC,
I have this Script Task which will make quite a few reebars jealous because it can. My first suggestion was "CASE all the way", then I was told it won't work due to the logic inherited in this task. As I have no clue about C# I left this and another Script Task for good and dealt with tables, indexes, TSQL statements and such.
Now I am back and still don't have a clue about C# but I'm not yet satisified I'll have to leave this for good so I was thinking like:
Well, others at times use DataTables within C# to add all rows before inserting them to the destination table, how much work would that possibly be to change the existing Script Task?
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
#endregion
namespace ST_d8974b4afbd74cabbccbfed3adf22eff
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* 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 task, according to whether or not your
* code needs to write to the variable. To add the variable, 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:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
// TODO: Add your code here
bool fireAgain = false;
String connectionString = (String)Dts.Variables["$Project::SCRIPT_CONNECTION_STRING"].Value;
String statement = "select s.Commission_ID, s.ModelTypeFull_ID, s.Effective_DateTime, DATEADD(dd, DATEDIFF(dd, 0, s.Effective_DateTime), 0) as Effective_Date, s.LifecycleStatus_ID, s.BusinessTypeGroup_ID, s.Ordering_Dealer_ID, s.Invoicing_Dealer_ID, s.Retailing_Dealer_ID, s.Disposability_ID, coalesce(vpc.V_Brand_Code, vvan.V_Brand_Code, vt.V_Brand_Code) as V_Brand_Code, cast(so1.SalesOrg_BusinessTypeGroup_Code as integer) as Ordering_Dealer_BTG_ID, cast(so2.SalesOrg_BusinessTypeGroup_Code as integer) as Retailing_Dealer_BTG_ID, coalesce(vt.Vehicle_SFTP_Product_Code, '-') as ProductGroup_ID, coalesce(vt.Vehicle_Cabin_Width_ID, -1) as Cabin_Width_ID, coalesce(vt.Vehicle_Floor_Variant_ID, '-') as Floor_Variant_ID, s.Value, s.SortKey from STG_F_Sales_Import s left outer join D_Vehicle_PC vpc on vpc.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID left outer join D_Vehicle_Van vvan on vvan.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID left outer join D_Vehicle_Truck vt on vt.Vehicle_Commission_ID = s.Commission_ID left outer join D_SalesOrg so1 on so1.SalesOrg_CustomerWS_ID = s.Ordering_Dealer_ID left outer join D_SalesOrg so2 on so2.SalesOrg_CustomerWS_ID = s.Retailing_Dealer_ID order by Commission_ID asc, Effective_DateTime desc, SortKey, s.LifecycleStatus_ID asc";
OleDbConnection sqlConn;
OleDbConnection sqlConnInsert;
OleDbCommand sqlComm;
OleDbDataReader dataReader;
try
{
//sqlConn = (OleDbConnection)cm.AcquireConnection(Dts.Transaction);
sqlConn = new OleDbConnection(connectionString);
sqlConn.Open();
sqlConnInsert = new OleDbConnection(connectionString);
sqlConnInsert.Open();
sqlComm = new OleDbCommand(statement, sqlConn);
dataReader = sqlComm.ExecuteReader();
dataReader.Read();
processData(dataReader, sqlConnInsert);
dataReader.Close();
sqlConn.Close();
Dts.Events.FireInformation(3, "Process Values", "Processing Done.", "", 0, ref fireAgain);
}
catch (Exception ex)
{
//MessageBox.Show("Can not open connection ! " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data + " : " + String.Empty);
Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace + " : " + ex.Data + " : " + ex.InnerException + " : " + ex.Source, "", 0, ref fireAgain);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
private void processData(OleDbDataReader InputRow, OleDbConnection sqlConnInsert)
{
// initialize variables (business logic)
String LastV = "";
DateTime LastDate = DateTime.MinValue;
DateTime LastEndDate = DateTime.MinValue;
DateTime NewEndDate = DateTime.MinValue;
int PK = 0;
// variables for flow control, statement generation
String insertStatement = "";
bool lastRow = false;
while (!lastRow)
{
// Input Row Fields
String Commission_ID = InputRow.GetString(0);
DateTime Effective_Date = InputRow.GetDateTime(3);
int LifecycleStatus_ID = InputRow.GetInt32(4);
Int32 Value = InputRow.GetInt32(16);
Int32 Ordering_Dealer_BTG_ID = InputRow.GetInt32(11);
Int32 BusinessTypeGroup_ID = InputRow.GetInt32(5);
Int32 Ordering_Dealer_ID = InputRow.GetInt32(6);
Int32 Retailing_Dealer_ID = InputRow.GetInt32(8);
Int32 Retailing_Dealer_BTG_ID = (InputRow.IsDBNull(12) ? 0 : InputRow.GetInt32(12));
// flush generated rows
insertStatement = writeRows(sqlConnInsert, insertStatement, false);
//-------------------------------------------------------------
// VChange
bool VChange;
if (Commission_ID != LastV)
{
VChange = true;
}
else
{
VChange = false;
}
//-------------------------------------------------------------
// LastV
LastV = Commission_ID;
/*
* orig:
$LastV := Commission_ID;
return Commission_ID;
* */
//-------------------------------------------------------------
// LastChangePerDay
bool LastChangePerDay;
if (VChange)
{
LastChangePerDay = true;
}
else
{
LastChangePerDay = (Effective_Date != LastDate);
}
//-------------------------------------------------------------
// LastDate
LastDate = Effective_Date;
//-------------------------------------------------------------
// EndDate
DateTime EndDate;
if (VChange)
{
LastEndDate = Effective_Date;
EndDate = DateTime.Now;
}
else
{
NewEndDate = LastEndDate;
LastEndDate = Effective_Date;
EndDate = NewEndDate;
}
//-------------------------------------------------------------
// StorageTime
int StorageTime;
if (
((LifecycleStatus_ID == 2 || LifecycleStatus_ID == 3 || LifecycleStatus_ID == 6 || LifecycleStatus_ID == 103) ||
(LifecycleStatus_ID == 102 && Value == 1) ||
(LifecycleStatus_ID == 107 && Value == -1)) && Ordering_Dealer_BTG_ID != 4)
{
TimeSpan duration = EndDate - Effective_Date;
StorageTime = duration.Days;
}
else
{
StorageTime = -1;
}
//-------------------------------------------------------------
// Corr_Retailing_Dealer_ID
int Corr_Retailing_Dealer_ID;
if (BusinessTypeGroup_ID == 2)
{
Corr_Retailing_Dealer_ID = Ordering_Dealer_ID;
}
else
{
Corr_Retailing_Dealer_ID = Retailing_Dealer_ID;
}
//-------------------------------------------------------------
// Corr_Retailing_Dealer_BTG_ID
int Corr_Retailing_Dealer_BTG_ID;
if (BusinessTypeGroup_ID == 2)
{
Corr_Retailing_Dealer_BTG_ID = Ordering_Dealer_BTG_ID;
}
else
{
Corr_Retailing_Dealer_BTG_ID = Retailing_Dealer_BTG_ID;
}
//-------------------------------------------------------------
// PK_Sales
PK = PK + 1;
// update insert statement
// insertStatement = insertStatement + "(" + Retailing_Dealer_ID + ", " + PrevSales_BTG_ID + ", NULL, " + Invoicing_Dealer_ID + ", " + Ordering_Dealer_ID + ", " + Update_LifecycleStatus_ID + ", " + Update_BTG_ID + ", " + PrevSales_LC_ID + ", " + (int)InputRow.GetInt64(0) + "),";
try
{
insertStatement = insertStatement +
" (" + PK + //<PK_Sales, bigint,>
", NULL " + //<FK_PrevSales, bigint,>
", " + "'" + Commission_ID + "'" + //<Commission_ID, nvarchar(255),>
", " + "'" + InputRow.GetString(1) + "'" + //<ModelTypeFull_ID, nvarchar(255),>
", " + (InputRow.IsDBNull(10) ? "NULL" : InputRow.GetInt64(10).ToString()) + //<V_Brand_Code, int,>
", " + "'" + InputRow.GetDateTime(2).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_DateTime, datetime,>
", " + "'" + InputRow.GetDateTime(3).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_Date, datetime,>
", " + InputRow.GetInt32(4) + //<LifecycleStatus_ID, int,>
", NULL " + //<PrevSales_LC_ID, int,>
", " + InputRow.GetInt32(5) + //<BusinessTypeGroup_ID, smallint,>
", NULL " + //<PrevSales_BTG_ID, smallint,>
", " + InputRow.GetInt32(6) + //<Ordering_Dealer_ID, int,>
", " + InputRow.GetInt32(11) + //<Ordering_Dealer_BTG_ID, int,>
", " + InputRow.GetInt32(7) + //<Invoicing_Dealer_ID, int,>
", " + Corr_Retailing_Dealer_ID + //<Retailing_Dealer_ID, int,>
", " + Corr_Retailing_Dealer_BTG_ID + //<Retailing_Dealer_BTG_ID, int,>
", " + InputRow.GetInt32(9) + //<Disposability_ID, int,>
", " + Convert.ToInt32(VChange) + //<LastVStatus, bit,>
", " + Convert.ToInt32(LastChangePerDay) + //<LastChangePerDay, bit,>
", " + ((StorageTime == -1) ? "NULL" : StorageTime.ToString()) + //<StorageTime, bigint,>
", " + "'" + EndDate.ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<EndDate, datetime,>
", " + Value + //<Value, bigint,>
", " + "'" + InputRow.GetString(13) + "'" + //<ProductGroup_ID, varchar(255),>
", " + InputRow.GetInt32(14) + //<Cabin_Width_ID, int,>
", " + "'" + InputRow.GetString(15) + "'" + //<Floor_Variant_ID, varchar(2),>
", NULL " + //<Ordering_Dealer_ID_Prev, int,>
", " + InputRow.GetInt32(17) + //<SortKey, int,>
"),";
}
catch (Exception ex)
{
MessageBox.Show("Statement construction error: " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data + " : " + ex.InnerException.ToString() + " : " + ex.TargetSite.ToString() + " : " + String.Empty);
// Dts.Events.FireInformation(3, "Insert Values", "Statement construction error: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
// Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
Dts.Events.FireError(1, insertStatement, ex.Message.ToString(), String.Empty, 0);
}
// fetch next Row, Attach Row to Output Buffer
lastRow = !(InputRow.Read());
}
// handle final commit
insertStatement = writeRows(sqlConnInsert, insertStatement, true);
}
private String writeRows(OleDbConnection sqlConnInsert, string insertStatement, bool forceCommit)
{
//throw new NotImplementedException();
if (!insertStatement.Equals(""))
{
if (forceCommit || insertStatement.Length > 200000)
{
insertStatement = insertStatement.Remove(insertStatement.Length - 1);
//MessageBox.Show(insertStatement);
OleDbCommand ic = new OleDbCommand(insertStatement, sqlConnInsert);
ic.ExecuteNonQuery();
}
else
{
return insertStatement;
}
}
// generate insert statement header:
return "INSERT INTO [dbo].[STG_F_Sales] ([PK_Sales],[FK_PrevSales],[Commission_ID],[ModelTypeFull_ID],[V_Brand_Code],[Effective_DateTime],[Effective_Date],[LifecycleStatus_ID],[PrevSales_LC_ID],[BusinessTypeGroup_ID],[PrevSales_BTG_ID],[Ordering_Dealer_ID],[Ordering_Dealer_BTG_ID],[Invoicing_Dealer_ID],[Retailing_Dealer_ID],[Retailing_Dealer_BTG_ID],[Disposability_ID],[LastVStatus],[LastChangePerDay],[StorageTime],[EndDate],[Value],[ProductGroup_ID],[Cabin_Width_ID],[Floor_Variant_ID],[Ordering_Dealer_ID_Prev],[SortKey]) VALUES ";
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
With our current approach we cannot insert more than 1000 Values per Statement which is what is bothering me mostly, if I could choose I'd go for BULK INSERT everything because it's a DWH and no one will be accessing it when this runs anyways.
So, are there major changes needed to the code or just change a few bits and pieces here and there? I would love to flat-line the CPU load to 100% if possible and sensible (= fast).
December 11, 2019 at 5:43 pm
without changing the logic of that task it would be possible to change it to be a script component (source) and instead of doing the inserts adding the values as a new row to a output buffer - and then use this component linked to its destination table to load directly in one go (batch mode, fast load or not)
see example at https://www.tutorialgateway.org/ssis-script-component-as-source/
December 12, 2019 at 10:37 am
sounds good, thanks for the input I'll try and see if I can get that running.
December 12, 2019 at 12:00 pm
It looks like the entire program could be changed to be a single SQL statement. If you are using SQL Server 2012 or higher you could use the LAG function. I haven't got time to do the entire statement but it would look something like this:
;WITH CTE AS
(
SELECT s.Commission_ID,
s.ModelTypeFull_ID,
s.Effective_DateTime,
DATEADD(dd, DATEDIFF(dd, 0, s.Effective_DateTime), 0) AS Effective_Date,
s.LifecycleStatus_ID,
s.BusinessTypeGroup_ID,
s.Ordering_Dealer_ID,
s.Invoicing_Dealer_ID,
s.Retailing_Dealer_ID,
s.Disposability_ID,
COALESCE(vpc.V_Brand_Code, vvan.V_Brand_Code, vt.V_Brand_Code) AS V_Brand_Code,
CAST(so1.SalesOrg_BusinessTypeGroup_Code AS integer) AS Ordering_Dealer_BTG_ID,
CAST(so2.SalesOrg_BusinessTypeGroup_Code AS integer) AS Retailing_Dealer_BTG_ID,
COALESCE(vt.Vehicle_SFTP_Product_Code, '-') AS ProductGroup_ID,
COALESCE(vt.Vehicle_Cabin_Width_ID, -1) AS Cabin_Width_ID,
COALESCE(vt.Vehicle_Floor_Variant_ID, '-') AS Floor_Variant_ID,
s.Value,
s.SortKey,
LAG(s.Commission_ID) OVER (ORDER BY s.Commission_ID ASC, s.Effective_DateTime DESC, s.SortKey, s.LifecycleStatus_ID ASC) LastCommission_ID
FROM STG_F_Sales_Import s
LEFT JOIN D_Vehicle_PC vpc
ON vpc.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID
LEFT JOIN D_Vehicle_Van vvan
ON vvan.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID
LEFT JOIN D_Vehicle_Truck vt
ON vt.Vehicle_Commission_ID = s.Commission_ID
LEFT JOIN D_SalesOrg so1
ON so1.SalesOrg_CustomerWS_ID = s.Ordering_Dealer_ID
LEFT JOIN D_SalesOrg so2
ON so2.SalesOrg_CustomerWS_ID = s.Retailing_Dealer_ID
)
INSERT INTO [dbo].[STG_F_Sales] (
[PK_Sales]
...
...
...
)
SELECT ...
CASE WHEN (LastCommission_ID IS NULL) OR (LastCommission_ID <> Commission_ID) THEN
ELSE
...
FROM CTE
December 12, 2019 at 4:35 pm
turns out it seems easier to convert a Script Task to a Script Component than it actually is - if you have no clue about these things like me.
I'd love to make a CTE out of this but I doubt I'll be able to get the logic behind some things like LastV and StorageTime inside a CTE.
I'll ask one of the guys maintaining that Script Task to help me out on this one for example
(InputRow.IsDBNull(10) ? "NULL" : InputRow.GetInt64(10).ToString())
is not something that'll work once I'm trying to do something like
OutputRowsBuffer.VehicleBrandCode = InputRow.IsDBNull(10) ? "NULL" : InputRow.GetInt64(10).ToString();
that one I actually don't even get at all, this is supposed to return an integer or NULL, not sure why GetInt64() is being called here.
this is a example based on your own code
Define the output columns as required, add the required variables as read only variables and within the script add the following code
from CreateNewOutputRows up to the end - leave the final "}"
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
bool fireAgain = false;
/*
Variables on a script componend are processed differently - they need to be added on the script editor window as a readonly or readwrite variable
it then becoves available within this script as a strong typed variable
so this
String connectionString = (String)Dts.Variables["$Project::SCRIPT_CONNECTION_STRING"].Value;
becomes
String connectionString = Variables.VariableName;
*/
String connectionString = ""; // see comments above
String statement = "select s.Commission_ID, s.ModelTypeFull_ID, s.Effective_DateTime, DATEADD(dd, DATEDIFF(dd, 0, s.Effective_DateTime), 0) as Effective_Date, s.LifecycleStatus_ID, s.BusinessTypeGroup_ID, s.Ordering_Dealer_ID, s.Invoicing_Dealer_ID, s.Retailing_Dealer_ID, s.Disposability_ID, coalesce(vpc.V_Brand_Code, vvan.V_Brand_Code, vt.V_Brand_Code) as V_Brand_Code, cast(so1.SalesOrg_BusinessTypeGroup_Code as integer) as Ordering_Dealer_BTG_ID, cast(so2.SalesOrg_BusinessTypeGroup_Code as integer) as Retailing_Dealer_BTG_ID, coalesce(vt.Vehicle_SFTP_Product_Code, '-') as ProductGroup_ID, coalesce(vt.Vehicle_Cabin_Width_ID, -1) as Cabin_Width_ID, coalesce(vt.Vehicle_Floor_Variant_ID, '-') as Floor_Variant_ID, s.Value, s.SortKey from STG_F_Sales_Import s left outer join D_Vehicle_PC vpc on vpc.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID left outer join D_Vehicle_Van vvan on vvan.Vehicle_ModelTypeFull_ID = s.ModelTypeFull_ID left outer join D_Vehicle_Truck vt on vt.Vehicle_Commission_ID = s.Commission_ID left outer join D_SalesOrg so1 on so1.SalesOrg_CustomerWS_ID = s.Ordering_Dealer_ID left outer join D_SalesOrg so2 on so2.SalesOrg_CustomerWS_ID = s.Retailing_Dealer_ID order by Commission_ID asc, Effective_DateTime desc, SortKey, s.LifecycleStatus_ID asc";
OleDbConnection sqlConn;
OleDbConnection sqlConnInsert;
OleDbCommand sqlComm;
OleDbDataReader dataReader;
try
{
//sqlConn = (OleDbConnection)cm.AcquireConnection(Dts.Transaction);
sqlConn = new OleDbConnection(connectionString);
sqlConn.Open();
sqlConnInsert = new OleDbConnection(connectionString);
sqlConnInsert.Open();
sqlComm = new OleDbCommand(statement, sqlConn);
dataReader = sqlComm.ExecuteReader();
dataReader.Read();
processData(dataReader, sqlConnInsert);
dataReader.Close();
sqlConn.Close();
// events done differently on a script component
// Dts.Events.FireInformation(3, "Process Values", "Processing Done.", "", 0, ref fireAgain);
this.ComponentMetaData.FireError(3, "Process Values", "Processing Done.", string.Empty, 0, out fireAgain);
}
catch (Exception ex)
{
//MessageBox.Show("Can not open connection ! " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data + " : " + String.Empty);
// events done differently on a script component
this.ComponentMetaData.FireError(1, ComponentMetaData.Name, ex.Message.ToString(), string.Empty, 0, out fireAgain);
}
}
private void processData(OleDbDataReader InputRow, OleDbConnection sqlConnInsert)
{
bool fireAgain = false;
// initialize variables (business logic)
String LastV = "";
DateTime LastDate = DateTime.MinValue;
DateTime LastEndDate = DateTime.MinValue;
DateTime NewEndDate = DateTime.MinValue;
int PK = 0;
// variables for flow control, statement generation
//String insertStatement = ""; // no longer required
bool lastRow = false;
while (!lastRow)
{
// Input Row Fields
String Commission_ID = InputRow.GetString(0);
DateTime Effective_Date = InputRow.GetDateTime(3);
int LifecycleStatus_ID = InputRow.GetInt32(4);
Int32 Value = InputRow.GetInt32(16);
Int32 Ordering_Dealer_BTG_ID = InputRow.GetInt32(11);
Int32 BusinessTypeGroup_ID = InputRow.GetInt32(5);
Int32 Ordering_Dealer_ID = InputRow.GetInt32(6);
Int32 Retailing_Dealer_ID = InputRow.GetInt32(8);
Int32 Retailing_Dealer_BTG_ID = (InputRow.IsDBNull(12) ? 0 : InputRow.GetInt32(12));
// flush generated rows -- this not required any more
//insertStatement = writeRows(sqlConnInsert, insertStatement, false);
//-------------------------------------------------------------
// VChange
bool VChange;
if (Commission_ID != LastV)
{
VChange = true;
}
else
{
VChange = false;
}
// Corr_Retailing_Dealer_BTG_ID
int Corr_Retailing_Dealer_BTG_ID;
if (BusinessTypeGroup_ID == 2)
{
Corr_Retailing_Dealer_BTG_ID = Ordering_Dealer_BTG_ID;
}
else
{
Corr_Retailing_Dealer_BTG_ID = Retailing_Dealer_BTG_ID;
}
/// remaining code removed up to the code that generated the insert statement - it can remain as it was before unchanged
// update insert statement
// insertStatement = insertStatement + "(" + Retailing_Dealer_ID + ", " + PrevSales_BTG_ID + ", NULL, " + Invoicing_Dealer_ID + ", " + Ordering_Dealer_ID + ", " + Update_LifecycleStatus_ID + ", " + Update_BTG_ID + ", " + PrevSales_LC_ID + ", " + (int)InputRow.GetInt64(0) + "),";
/*
code now inserts the input record values and the calculated values onto a output buffer row
so the old insertstatement string build can be removed
*/
try
{
OutputRowsBuffer.AddRow();
/*
in order to set a output buffer column to null we need to set its isnull properte to true
set the following columns as example
note that although we define them on the output column collection with the "_" SSIs will remove them when defining the variables
ProductGroup_ID-- string -- InputRow.GetString(13)
Corr_Retailing_Dealer_BTG_ID-- int -- calculated variable
Ordering_Dealer_ID --int -- previous assigned variable from input row
VChange -- bool -- calculated variable
*/
// set ProductGroup_ID
if (InputRow.IsDBNull(13))
{
OutputRowsBuffer.ProductGroupID_IsNull = true;
}
else
{
OutputRowsBuffer.ProductGroupID = InputRow.GetString(13);
}
// set Corr_Retailing_Dealer_BTG_ID
// if it had been defined as a nullable int we should check for null
//if (Corr_Retailing_Dealer_BTG_ID == null)
//{
// OutputRowsBuffer.CorrRetailingDealerBTGID_IsNull = true;
//}
//else
//{
// OutputRowsBuffer.CorrRetailingDealerBTGID = Corr_Retailing_Dealer_BTG_ID;
//}
OutputRowsBuffer.RetailingDealerBTGID = Corr_Retailing_Dealer_BTG_ID;
// set Ordering_Dealer_ID
// if it had been defined as a nullable int we should check for null
OutputRowsBuffer.OrderingDealerID = Ordering_Dealer_ID;
// set VChange -- as it is a boolean it can be assigned directly without any null check
OutputRowsBuffer.VChange = VChange;
}
catch (Exception ex)
{
// change this to be based on new processing - no longer a statement build error
MessageBox.Show("Statement construction error: " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data + " : " + ex.InnerException.ToString() + " : " + ex.TargetSite.ToString() + " : " + String.Empty);
// Dts.Events.FireInformation(3, "Insert Values", "Statement construction error: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
// Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
// events done differently on a script component
this.ComponentMetaData.FireError(1,ComponentMetaData.Name, ex.Message.ToString(), string.Empty, 0, out fireAgain);
}
// fetch next Row, Attach Row to Output Buffer
lastRow = !(InputRow.Read());
}
}
December 13, 2019 at 3:57 pm
Hi Frederico,
thank you very much, you're awesome! 🙂
I still have one minor issue, I think I miss some sort of ProcessingEnds handler or something because he does process all the rows and Shows me success for both the Script Component Source and OLEDB-Destination but tells me the DFT failed.
The failure message is:
SSIS.Pipeline Information: Execution Begins.
Process Values: Error: Processing Done.
OLE DB Destination: The final commit for "OLE DB-Destination" has begun. (and completed)
the last part of my code (based on frederico's help) looks like this:
AusgabeZeileBuffer.SortKey = InputRow.GetInt32(17);
// set Corr_Retailing_Dealer_BTG_ID
// if it had been defined as a nullable int we should check for null
//if (Corr_Retailing_Dealer_BTG_ID == null)
//{
// AusgabeZeileBuffer.CorrRetailingDealerBTGID_IsNull = true;
//}
//else
//{
// AusgabeZeileBuffer.CorrRetailingDealerBTGID = Corr_Retailing_Dealer_BTG_ID;
//}
//AusgabeZeileBuffer.RetailingDealerBTGID = Corr_Retailing_Dealer_BTG_ID;
// set Ordering_Dealer_ID
// if it had been defined as a nullable int we should check for null
//AusgabeZeileBuffer.OrderingDealerID = Ordering_Dealer_ID;
// set VChange -- as it is a boolean it can be assigned directly without any null check
}
catch (Exception ex)
{
// change this to be based on new processing - no longer a statement build error
MessageBox.Show("Value processing error: " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data + " : " + ex.InnerException.ToString() + " : " + ex.TargetSite.ToString() + " : " + String.Empty);
// Dts.Events.FireInformation(3, "Insert Values", "Statement construction error: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
// Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
// events done differently on a script component
this.ComponentMetaData.FireError(1, ComponentMetaData.Name, ex.Message.ToString(), string.Empty, 0, out fireAgain);
}
// fetch next Row, Attach Row to Output Buffer
lastRow = !(InputRow.Read());
}
}
}
so, am I right? There is something missing somewhere and I think it's related to letting me know that everything has gone well?
December 14, 2019 at 5:13 pm
can you post the full code - in pm if required - it does look ok and I dont see an error on its own
December 16, 2019 at 8:04 am
found it myself,
this.ComponentMetaData.FireInformation(3, "Process Values", "Processing Done.", "", 0, ref fireAgain);
this is what I need instead of FireError in the first occurence
December 16, 2019 at 11:28 am
great - do get back to us with performance stats - although as mentioned before doing it SQL alone would also work.
with this you should be able to process all data in one go though.
December 16, 2019 at 4:35 pm
I will get back with performance numbers tomorrow (once the daily load is done). I've changed 2 Script Tasks with your help to Script Components, one does show quite dramatic improvements, the other one doesn't because it relies on a function. I'm looking into that to change that, too.
December 18, 2019 at 1:44 pm
So I'm back with performance numbers:
Initially the whole load would take between 4 and 5 Hours, after changing one Script Task to a Script Component (see above) and another Script Task including a function call was replaced by pure TSQL and the results make me laugh (because I spent the last year ranting about those crappy C# things):
1 hour and 22 minutes is what it took today, although I must admit my TSQL replacement has an issue with 11 (out of about 4 million) rows so we might still see some minor increase in runtime which still makes it a reduction of at least 50% in regards to the overall duration. I told my boss yesterday if this works out as planned I'm so going to get drunk tonight.
All I can say: it's his own fault that he didn't object to this so tomorrow (which is going to be my last working day unless something breaks during holiday season) I'm glad I have a short working day 🙂
Again thanks to everyone figuring / changing this to something that at least uses some resources while processing and not appears to be sleeping all day long because of RBAR processing.
December 18, 2019 at 2:29 pm
good feedback.
If you don't mind do also gives the SSQL function and how its used - we may be able to help with it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply