/* 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 System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Comp3 = CustomComponents.UnpackDecimal;
using Comp = CustomComponents.HexTranslator;
using TrimThatColumn = CustomComponents.TrimThatColumn;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using ScriptTask = Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using Microsoft.CSharp.RuntimeBinder;
using System.Collections.Generic;
using System.Text;
#endregion
namespace ST_8522b938b0114bac9e084e6e5c77032b
{
/// <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>
#region GlobalVariables
//Global Variables
Package myPackage = new Package();
// Application
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
Variables vars = null;
StringBuilder Load2MasterCmd = new StringBuilder();
StringBuilder Master2Oracle = new StringBuilder();
String SaveDirectory = "";
int BDELoaderId = 0;
#endregion
public void Main()
{
// TODO: Add your code here
try
{
//Reference Variables
Dts.VariableDispenser.LockForRead("User::objColumnInfo");
Dts.VariableDispenser.LockForRead("User::objMasterColumnInfo");
Dts.VariableDispenser.LockForRead("User::strFileName");
Dts.VariableDispenser.LockForRead("User::strTabName");
Dts.VariableDispenser.LockForRead("User::strTableName");
Dts.VariableDispenser.LockForRead("User::intGroupNumber");
Dts.VariableDispenser.LockForRead("User::strDatabaseName");
Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
Dts.VariableDispenser.LockForRead("User::strServerName");
Dts.VariableDispenser.LockForRead("User::strBatchId");
Dts.VariableDispenser.LockForRead("User::strSaveDirectory");
Dts.VariableDispenser.LockForRead("User::strSqlCmd");
Dts.VariableDispenser.LockForRead("User::strFileHeader");
Dts.VariableDispenser.LockForRead("User::intBDELoaderID");
Dts.VariableDispenser.LockForWrite("User::intRecordCount");
Dts.VariableDispenser.LockForWrite("User::strArchiveDirectory");
Dts.VariableDispenser.LockForWrite("User::bolIsLoaded");
Dts.VariableDispenser.LockForRead("User::bolUseDeltaByte");
Dts.VariableDispenser.LockForWrite("User::bigDuration");
Dts.VariableDispenser.LockForRead("User::dtSnapshotDate");
Dts.VariableDispenser.LockForRead("User::strLookupQuery");
//Get variable collector
Dts.VariableDispenser.GetVariables(ref vars);
BDELoaderId = (int)vars["User::intBDELoaderID"].Value;
// SnapshotDate Variable
DateTime snapshot = (DateTime)vars["User::dtSnapshotDate"].Value;
//Add Count Variable to Package
Microsoft.SqlServer.Dts.Runtime.Variable intFileRowCount = myPackage.Variables.Add("intFileRowCount", false, "User", 0);
Microsoft.SqlServer.Dts.Runtime.Variable intBDELoaderId = myPackage.Variables.Add("intBDELoaderId", false, "User", BDELoaderId);
Microsoft.SqlServer.Dts.Runtime.Variable SnapShot_Date = myPackage.Variables.Add("dtSnapshotDate", false, "User",snapshot);
Microsoft.SqlServer.Dts.Runtime.Variable Load_Date = myPackage.Variables.Add("dtLoadDate", false, "User", DateTime.Now);
var LookupJoinColumns = new String[] { "LN_NO" };
//Truncate table Command Variable
StringBuilder TruncateCommand = new StringBuilder();
TruncateCommand.Append(vars["User::strSqlCmd"].Value.ToString() + " ");
TruncateCommand.Append("[Load]." + vars["User::strTableName"].Value.ToString());
String TabName = vars["User::strTabName"].Value.ToString().Replace(" ","");
String LookupQuery = vars["User::strLookupQuery"].Value.ToString();
// Set Package Name
myPackage.Name = "ssisRawFileToLoad_" + TabName;
String LoadDirectory = vars["User::strLoadDirectory"].Value.ToString();
SaveDirectory = vars["User::strSaveDirectory"].Value.ToString();
String LoadTableName = "[Load]." + vars["User::strTableName"].Value.ToString(); // Using the master
String FileName = vars["User::strFileName"].Value.ToString();
String FlatFileConnectionString = LoadDirectory + "\\" + FileName;
String SQLCmd = vars["User::strSqlCmd"].Value.ToString();
String Truncate = TruncateCommand.ToString();
// Local variable for File Path
Microsoft.SqlServer.Dts.Runtime.Variable strLoadFilePath = myPackage.Variables.Add("strFullFilePath", false, "User", FlatFileConnectionString);
Microsoft.SqlServer.Dts.Runtime.Variable strArchiveFilePath = myPackage.Variables.Add("strArchiveDirectory", false, "User", vars["User::strArchiveDirectory"].Value.ToString());
// Joins Variables
PipelineWrapper.IDTSPath100 DeriveColumnToCount;
PipelineWrapper.IDTSPath100 UnionAllToTrimmer;
PipelineWrapper.IDTSPath100 SourceToDeriveColumn;
PipelineWrapper.IDTSPath100 CountToDistributor;
PipelineWrapper.IDTSPath100 UnpackToUnpackInt0;
PipelineWrapper.IDTSPath100 UnpackToUnpackInt1;
PipelineWrapper.IDTSPath100 UnpackToUnpackInt2;
PipelineWrapper.IDTSPath100 UnpackToUnpackInt3;
PipelineWrapper.IDTSPath100 UnpackToUnpackInt4;
PipelineWrapper.IDTSPath100 DistributorToUnpack0;
PipelineWrapper.IDTSPath100 DistributorToUnpack1;
PipelineWrapper.IDTSPath100 DistributorToUnpack2;
PipelineWrapper.IDTSPath100 DistributorToUnpack3;
PipelineWrapper.IDTSPath100 DistributorToUnpack4;
PipelineWrapper.IDTSPath100 Unpack0ToUnionAll;
PipelineWrapper.IDTSPath100 Unpack1ToUnionAll;
PipelineWrapper.IDTSPath100 Unpack2ToUnionAll;
PipelineWrapper.IDTSPath100 Unpack3ToUnionAll;
PipelineWrapper.IDTSPath100 TrimmerToLookup;
PipelineWrapper.IDTSPath100 LookupMatchToUnionAll;
PipelineWrapper.IDTSPath100 LookupNotMatchToUnionAll;
PipelineWrapper.IDTSPath100 UnionAllToDestination;
// Get Columns Metadata
DataTable ColumnInfo = ReadColumnInfo(vars["User::objColumnInfo"].Value);
// DataTable MasterColumnInfo = ReadColumnInfo(vars["User::objMasterColumnInfo"].Value);
// vars["User::objMasterColumnInfo"].Value = null;
//Create Fixed Width Connection
ConnectionManager FixedWidth = AddFixedWith(ColumnInfo, vars["User::strTabName"].Value.ToString().Replace(" ",""), FlatFileConnectionString);
// GenerateInsert(MasterColumnInfo);
// String MasterToOracle = Master2Oracle.ToString();
//Create OLEDB Connection
ConnectionManager Destination = AddOLEDBConnection("destination", Dts.Connections["destination"].ConnectionString.ToString());
ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
ConnectionManager Source = AddOLEDBConnection("source", Dts.Connections["source"].ConnectionString.ToString());
//Add Logging reference
AddLogging(MSDB.Name, true);
// Add SQLTask Component
Executable execSqlTask = AddSQLExecutable("STOCK:SQLTASk");
//Wrap the executable in a TaskHost
AddSQLTask(execSqlTask, "Truncate Load Table", "destination", Truncate);
//Add Sequence Container
Executable execSequence = AddSQLExecutable("STOCK:Sequence");
Microsoft.SqlServer.Dts.Runtime.Sequence seqSequence = (Microsoft.SqlServer.Dts.Runtime.Sequence)execSequence;
//Sequence Properties
seqSequence.Name = "UnpackDecimals Loader";
seqSequence.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.UseParentSetting;
seqSequence.Description = "Unpack Decimal Container";
//Set precedence Constraint
Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
SqlTask2Sequece.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
//set Precedence of Load2Master
// Add SQLStask to sequence
// Executable execSqlTaskMasterInfo = seqSequence.Executables.Add("STOCK:SQLTask");
//Executable execSqlTaskUpdateBDELoader = seqSequence.Executables.Add("STOCK.SQLTask");
// Add WorkFlow Component
Executable execDataFlow = seqSequence.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execDataFlow;
thDataFlow.Name = "LoadBDE";
PipelineWrapper.MainPipe DataFlow = (PipelineWrapper.MainPipe)thDataFlow.InnerObject;
// SQLTASK This may need to be remove ??????????????????????????????????????????????????????????????????????????
// AddSQLTask(execSqlTaskMasterInfo, "Load2MasterCasting", "destination", Load2MasterCmd.ToString());
// AddSQLTask(execSqlTaskUpdateBDELoader, "UpDataBDELoader", "host", Load2MasterCmd.ToString());
// set precedence of workfolw
// Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint WorkFlow2Load2Master = seqSequence.PrecedenceConstraints.Add(execDataFlow, execSqlTaskMasterInfo);
// WorkFlow2Load2Master.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
//Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint Load2Master2UpdateBDE = seqSequence.PrecedenceConstraints.Add(execSqlTaskMasterInfo, execSqlTaskUpdateBDELoader);
//Load2Master2UpdateBDE.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
//Add FileSource Componnent
PipelineWrapper.IDTSComponentMetaData100 FileSource = AddFileSourceComponent(DataFlow, FixedWidth, TabName);
//dictionary
//Add Derivide Column Componnent
PipelineWrapper.IDTSComponentMetaData100 icmdDeriveColumn = AddComponentMetadata(DataFlow, "DeriveColumn");
icmdDeriveColumn.ComponentClassID = "DTSTransform.DerivedColumn";
PipelineWrapper.CManagedComponentWrapper mcwDeriveColumn = icmdDeriveColumn.Instantiate();
mcwDeriveColumn.ProvideComponentProperties();
icmdDeriveColumn.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
icmdDeriveColumn.InputCollection[0].HasSideEffects = false;
Reinitiaze(mcwDeriveColumn);
PipelineWrapper.IDTSOutputColumn100 snapshotDate = icmdDeriveColumn.OutputCollection[0].OutputColumnCollection.New();
snapshotDate.Name = "Snapshot_Date";
snapshotDate.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DATE,0,0,0,0);
snapshotDate.ExternalMetadataColumnID = 0;
PipelineWrapper.IDTSOutputColumn100 LoadDate = icmdDeriveColumn.OutputCollection[0].OutputColumnCollection.New();
LoadDate.Name = "Load_Date";
LoadDate.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DATE, 0, 0, 0, 0);
LoadDate.ExternalMetadataColumnID = 1;
snapshotDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
snapshotDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
PipelineWrapper.IDTSCustomProperty100 outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
LoadDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
LoadDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
PipelineWrapper.IDTSCustomProperty100 outLoadDate = LoadDate.CustomPropertyCollection.New();
outSnapShotDate.Name = "Expression";
outSnapShotDate.Value = "@[User::dtSnapshotDate]";
outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
outSnapShotDate.Name = "FriendlyExpression";
outSnapShotDate.Value = "@[User::dtSnapshotDate]";
outLoadDate.Name = "Expression";
outLoadDate.Value = "@[User::dtLoadDate]";
outLoadDate = LoadDate.CustomPropertyCollection.New();
outLoadDate.Name = "FriendlyExpression";
outLoadDate.Value = "@[User::dtLoadDate]";
// Add Lookup Component
PipelineWrapper.IDTSComponentMetaData100 icmdLookup = AddComponentMetadata(DataFlow, "SubClientId_Lookup");
icmdLookup.ComponentClassID = "{671046B0-AA63-4C9F-90E4-C06E0B710CE3}";//"";
PipelineWrapper.CManagedComponentWrapper mcwLookup = icmdLookup.Instantiate();
icmdLookup.ValidateExternalMetadata = true;
mcwLookup.ProvideComponentProperties();
icmdLookup.Name ="SubClientID_Lookup";
//Add RowCount Component
PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(DataFlow, "RowCountComponent");
icmdRowCount.ComponentClassID = "DTSTransform.RowCount";
PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
mcwRowCount.ProvideComponentProperties();
mcwRowCount.SetComponentProperty("VariableName", "User::intFileRowCount");
// Add Trim Component
//Add TrimThatColumn component
PipelineWrapper.IDTSComponentMetaData100 TrimThatColumn0 = AddTrimThatColumn(DataFlow, "TrimThatColumn");
TrimThatColumn0.ComponentClassID = "{874F7595-FB5F-40FF-96AF-FBFF8250E3EF}";
PipelineWrapper.CManagedComponentWrapper mcwTrimThatColumn0 = TrimThatColumn0.Instantiate();
mcwTrimThatColumn0.ProvideComponentProperties();
// Add Distributor Component
PipelineWrapper.IDTSComponentMetaData100 Distributor = AddComponentMetadata(DataFlow, "dataDistributor");
Distributor.ComponentClassID = "{F06C606C-5980-460C-B2AF-B0ECA031B7A4}";
PipelineWrapper.CManagedComponentWrapper mcwDistributor = Distributor.Instantiate();
mcwDistributor.ProvideComponentProperties();
//Add Union All Component
PipelineWrapper.IDTSComponentMetaData100 UnionAll = AddComponentMetadata(DataFlow, "UnionAll");
UnionAll.ComponentClassID = "{B594E9A8-4351-4939-891C-CFE1AB93E925}";
PipelineWrapper.CManagedComponentWrapper mcwUnialAll = UnionAll.Instantiate();
mcwUnialAll.ProvideComponentProperties();
// Union All For Lookup input and output
PipelineWrapper.IDTSComponentMetaData100 LookupUnionAll = AddComponentMetadata(DataFlow, "LookupUnionAll");
LookupUnionAll.ComponentClassID = "{B594E9A8-4351-4939-891C-CFE1AB93E925}";
PipelineWrapper.CManagedComponentWrapper mcwLookupUnialAll = LookupUnionAll.Instantiate();
mcwLookupUnialAll.ProvideComponentProperties();
LookupUnionAll.Name = "LookupUnionAll";
// Add Comp Hex translator
PipelineWrapper.IDTSComponentMetaData100 UnpackInteger0 = AddUnpackIntegers(DataFlow, "HexToInt00");
PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger0 = UnpackInteger0.Instantiate();
cmwUnpackInteger0.ProvideComponentProperties();
UnpackInteger0.Name = "HexToInt00";
// II
PipelineWrapper.IDTSComponentMetaData100 UnpackInteger1 = AddUnpackIntegers(DataFlow, "HexToInt01");
PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger1 = UnpackInteger1.Instantiate();
cmwUnpackInteger1.ProvideComponentProperties();
UnpackInteger1.Name = "HexToInt01";
//III
PipelineWrapper.IDTSComponentMetaData100 UnpackInteger2 = AddUnpackIntegers(DataFlow, "HexToInt02");
PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger2 = UnpackInteger2.Instantiate();
cmwUnpackInteger2.ProvideComponentProperties();
UnpackInteger2.Name = "HexToInt02";
//IV
PipelineWrapper.IDTSComponentMetaData100 UnpackInteger3 = AddUnpackIntegers(DataFlow, "HexToInt03");
PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger3 = UnpackInteger3.Instantiate();
cmwUnpackInteger3.ProvideComponentProperties();
UnpackInteger3.Name = "HexToInt03";
//V
//IV
PipelineWrapper.IDTSComponentMetaData100 UnpackInteger4 = AddUnpackIntegers(DataFlow, "HexToInt04");
PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger4 = UnpackInteger4.Instantiate();
cmwUnpackInteger3.ProvideComponentProperties();
UnpackInteger4.Name = "HexToInt04";
// Add Unpack Decimal Custom Component
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal0 = AddUnpackDecimal(DataFlow, "Unpack0Decimals");
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal0 = UnpackDecimal0.Instantiate();
cmwUnpackDecimal0.ProvideComponentProperties();
UnpackDecimal0.Name = "Unpack0 Decimals";
// II
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal2 = AddUnpackDecimal(DataFlow, "Unpack2Decimals");
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal2 = UnpackDecimal2.Instantiate();
cmwUnpackDecimal2.ProvideComponentProperties();
UnpackDecimal2.Name = "Unpack2 Decimals";
//III
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal3 = AddUnpackDecimal(DataFlow, "Unpack3Decimals");
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal3 = UnpackDecimal3.Instantiate();
cmwUnpackDecimal3.ProvideComponentProperties();
UnpackDecimal3.Name = "Unpack3 Decimals";
//IV
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal1 = AddUnpackDecimal(DataFlow, "Unpack1Decimals");
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal1 = UnpackDecimal1.Instantiate();
cmwUnpackDecimal1.ProvideComponentProperties();
UnpackDecimal1.Name = "Unpack1 Decimals";
//V
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal4 = AddUnpackDecimal(DataFlow, "Unpack1Decimals");
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal4 = UnpackDecimal1.Instantiate();
cmwUnpackDecimal4.ProvideComponentProperties();
UnpackDecimal4.Name = "Unpack4 Decimals";
//Add OLEDB Destination Componnent
PipelineWrapper.IDTSComponentMetaData100 icmDestination = AddComponentMetadata(DataFlow, "destination");
icmDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
icmDestination.ValidateExternalMetadata = true;
PipelineWrapper.CManagedComponentWrapper mcwDestination = icmDestination.Instantiate();
// tblDestination
mcwDestination.ProvideComponentProperties();
icmDestination.Name = "destination";
icmDestination.Description = "destination";
icmDestination.RuntimeConnectionCollection[0].ConnectionManagerID = Destination.ID;
icmDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(Destination);
mcwDestination.SetComponentProperty("AccessMode", 3);//FastLoad
mcwDestination.SetComponentProperty("OpenRowset", LoadTableName);//tblDestination);
mcwDestination.Validate();
mcwDestination.SetComponentProperty("FastLoadKeepIdentity", false);
mcwDestination.SetComponentProperty("FastLoadMaxInsertCommitSize",100000);//FastLoadMaxCommitSize);
mcwDestination.SetComponentProperty("FastLoadKeepNulls", false);
mcwDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
Reinitiaze(mcwDestination);
//Join Source To UnpackDecimal Component
SourceToDeriveColumn = DataFlow.PathCollection.New();
// File Source To Count
SourceToDeriveColumn.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0],icmdDeriveColumn.InputCollection[0]);
DeriveColumnToCount = DataFlow.PathCollection.New();
//Count to Distributor
DeriveColumnToCount.AttachPathAndPropagateNotifications(icmdDeriveColumn.OutputCollection[0], icmdRowCount.InputCollection[0]);
// Count to Distributor
CountToDistributor = DataFlow.PathCollection.New();
CountToDistributor.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], Distributor.InputCollection[0]);
// Distributor Settings
DistributorToUnpack0 = DataFlow.PathCollection.New();
DistributorToUnpack0.AttachPathAndPropagateNotifications(Distributor.OutputCollection[0], UnpackInteger0.InputCollection[0]);
DistributorToUnpack1 = DataFlow.PathCollection.New();
DistributorToUnpack1.AttachPathAndPropagateNotifications(Distributor.OutputCollection[1], UnpackInteger1.InputCollection[0]);
DistributorToUnpack2 = DataFlow.PathCollection.New();
DistributorToUnpack2.AttachPathAndPropagateNotifications(Distributor.OutputCollection[2], UnpackInteger2.InputCollection[0]);
DistributorToUnpack3 = DataFlow.PathCollection.New();
DistributorToUnpack3.AttachPathAndPropagateNotifications(Distributor.OutputCollection[3], UnpackInteger3.InputCollection[0]);
DistributorToUnpack4 = DataFlow.PathCollection.New();
DistributorToUnpack4.AttachPathAndPropagateNotifications(Distributor.OutputCollection[4], UnpackInteger4.InputCollection[0]);
//Join Unpack to unpackint
UnpackToUnpackInt0 = DataFlow.PathCollection.New();
UnpackToUnpackInt0.AttachPathAndPropagateNotifications(UnpackInteger0.OutputCollection[0], UnpackDecimal0.InputCollection[0]);
UnpackToUnpackInt1 = DataFlow.PathCollection.New();
UnpackToUnpackInt1.AttachPathAndPropagateNotifications(UnpackInteger1.OutputCollection[0], UnpackDecimal1.InputCollection[0]);
UnpackToUnpackInt2 = DataFlow.PathCollection.New();
UnpackToUnpackInt2.AttachPathAndPropagateNotifications(UnpackInteger2.OutputCollection[0], UnpackDecimal2.InputCollection[0]);
UnpackToUnpackInt3 = DataFlow.PathCollection.New();
UnpackToUnpackInt3.AttachPathAndPropagateNotifications(UnpackInteger3.OutputCollection[0], UnpackDecimal3.InputCollection[0]);
UnpackToUnpackInt4 = DataFlow.PathCollection.New();
UnpackToUnpackInt4.AttachPathAndPropagateNotifications(UnpackInteger4.OutputCollection[0], UnpackDecimal4.InputCollection[0]);
//Set Properties for Bynary Fields
SetFlatFileProperty(FileSource);
// Unpack Comp Settings
SelectDecimalColumns(UnpackInteger0, ColumnInfo, "IsComp");
SelectDecimalColumns(UnpackInteger1, ColumnInfo, "IsComp");
SelectDecimalColumns(UnpackInteger2, ColumnInfo, "IsComp");
SelectDecimalColumns(UnpackInteger3, ColumnInfo, "IsComp");
SelectDecimalColumns(UnpackInteger4, ColumnInfo, "IsComp");
// Unpack settings
SelectDecimalColumns(UnpackDecimal0, ColumnInfo,"IsComp3");
SetUnpackDecimalScale(UnpackDecimal0, ColumnInfo);
SelectDecimalColumns(UnpackDecimal1, ColumnInfo, "IsComp3");
SetUnpackDecimalScale(UnpackDecimal1, ColumnInfo);
SelectDecimalColumns(UnpackDecimal2, ColumnInfo, "IsComp3");
SetUnpackDecimalScale(UnpackDecimal2, ColumnInfo);
SelectDecimalColumns(UnpackDecimal3, ColumnInfo, "IsComp3");
SetUnpackDecimalScale(UnpackDecimal3, ColumnInfo);
SelectDecimalColumns(UnpackDecimal4, ColumnInfo, "IsComp3");
SetUnpackDecimalScale(UnpackDecimal4, ColumnInfo);
// Join Unial All
// Distributor Settings
Unpack0ToUnionAll = DataFlow.PathCollection.New();
Unpack0ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal0.OutputCollection[0], UnionAll.InputCollection[0]);
Unpack1ToUnionAll = DataFlow.PathCollection.New();
Unpack1ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal1.OutputCollection[0], UnionAll.InputCollection[1]);
Unpack2ToUnionAll = DataFlow.PathCollection.New();
Unpack2ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal2.OutputCollection[0], UnionAll.InputCollection[2]);
Unpack3ToUnionAll = DataFlow.PathCollection.New();
Unpack3ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal3.OutputCollection[0], UnionAll.InputCollection[3]);
Unpack3ToUnionAll = DataFlow.PathCollection.New();
Unpack3ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal4.OutputCollection[0], UnionAll.InputCollection[4]);
//Join Unpack Decimal to Destination
UnionAllToTrimmer = DataFlow.PathCollection.New();
UnionAllToTrimmer.AttachPathAndPropagateNotifications(UnionAll.OutputCollection[0], TrimThatColumn0.InputCollection[0]);
TrimmerToLookup = DataFlow.PathCollection.New();
TrimmerToLookup.AttachPathAndPropagateNotifications(TrimThatColumn0.OutputCollection[0],icmdLookup.InputCollection[0] );
// LookUp Configuration
icmdLookup.RuntimeConnectionCollection[0].ConnectionManagerID = Source.ID;
icmdLookup.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(Source);
//Lookup Cache Type - Full = 0, Partial = 1, None = 2
mcwLookup.SetComponentProperty("CacheType", 0);//Full
mcwLookup.SetComponentProperty("SqlCommand", LookupQuery.ToString());
mcwLookup.SetComponentProperty("NoMatchBehavior", 1); // Redirect Not Match output
Reinitiaze(mcwLookup);
MappColumnsLookup(icmdLookup, mcwLookup, "LN_NO");
/*
// Mapping lookup LN_NO to source LN_NO query
//Input: Contains the collection of columns that represents the data provided to a component
PipelineWrapper.IDTSInput100 lookupInput = icmdLookup.InputCollection[0];
//Virtual Input: Represents the columns available to a component from the upstream component.
PipelineWrapper.IDTSVirtualInput100 lookupVirtualInput = lookupInput.GetVirtualInput();
//Here the lookup will be done by joining [Transaction_Info].Emp_ID column with [Employee_Dim].emp_id column
//SetUsageType: used to select the columns from the upstream component in the data flow that are used by the component.
PipelineWrapper.IDTSInputColumn100 lookupInputColumn = mcwLookup.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection["LN_NO"].LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
//JoinToReferenceColumn = The name of the column in the reference table to which a source column joins.
//SetInputColumnProperty = Sets the custom property of the input column "LN_NO" and the custom property is "JoinToReferenceColumn" with value "LN_NO"
mcwLookup.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "ln_no");
//Note: The above code can be iterated in a loop for more than one joining columns.
//To replace the existing input column with the reference column, use "CopyFromReferenceColumn" property in the above code.
//For replacement of existing column, the input column should be set as DTSUsageType.UT_READWRITE
*/
PipelineWrapper.IDTSOutput100 lookupOutput = icmdLookup.OutputCollection[0];
// insert columns from refrence table
PipelineWrapper.IDTSOutputColumn100 lookupOutputColumn = mcwLookup.InsertOutputColumnAt(lookupOutput.ID, 0, "SubClient_ID", "");
mcwLookup.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "SubClient_ID");
LookupMatchToUnionAll = DataFlow.PathCollection.New();
LookupMatchToUnionAll.AttachPathAndPropagateNotifications(icmdLookup.OutputCollection[0], LookupUnionAll.InputCollection[0]);
//Lookup Not Match Mapping
LookupNotMatchToUnionAll = DataFlow.PathCollection.New();
LookupNotMatchToUnionAll.AttachPathAndPropagateNotifications(icmdLookup.OutputCollection[1], LookupUnionAll.InputCollection[1]);
UnionAllToDestination = DataFlow.PathCollection.New();
UnionAllToDestination.AttachPathAndPropagateNotifications(LookupUnionAll.OutputCollection[0], icmDestination.InputCollection[0]);
MappColumns(icmDestination, mcwDestination);
DTSExecResult result = myPackage.Execute();
DTSExecStatus status = myPackage.ExecutionStatus;
vars["User::bigDuration"].Value = myPackage.ExecutionDuration;
vars["User::intRecordCount"].Value = intFileRowCount.Value;
if ((result == DTSExecResult.Failure) || (myPackage.ExecutionStatus == DTSExecStatus.Abend))
{
// Package Failure on Error
vars["User::bolIsLoaded"].Value = false;
throw new System.InvalidCastException("Generated Package Failure");
// Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
vars["User::intRecordCount"].Value = myPackage.Variables["User::intFileRowCount"].Value;
vars["User::bolIsLoaded"].Value = true;
SaveSSIS(SaveDirectory, myPackage.Name.ToString());
myPackage.Dispose();
// TODO: Add your code here
vars.Unlock();
//ColumnInfo = null;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch (Exception e)
{
String packageName = myPackage.Name.ToString();
myPackage.Name = "Error" + packageName;
SaveSSIS(@"E:\\BDELoader\\ssis\\failure\\", myPackage.Name.ToString());
myPackage.Dispose();
Dts.Events.FireError(-1, "", "Ssis Failure:" + packageName + " Error:" + e.Message.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
public void AddLogging(String ConnectionName, Boolean Enable)
{
LogProvider pkLogging;
pkLogging = myPackage.LogProviders.Add("DTS.LogProviderSQLServer");
pkLogging.Name = "Log Provider For SQL SERVER";
pkLogging.ConfigString = ConnectionName;
pkLogging.Description = "SQL Server Logging ";
myPackage.LoggingOptions.SelectedLogProviders.Add(pkLogging);
myPackage.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
myPackage.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
switch (Enable)
{
case true:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
break;
case false:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
default:
myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
break;
}
}
private void SetUnpackDecimalScale(PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal, DataTable Metadata)
{
PipelineWrapper.IDTSInputCollection100 icolCollection = UnpackDecimal.InputCollection;
PipelineWrapper.IDTSInput100 icol = icolCollection[0];
PipelineWrapper.IDTSInputColumnCollection100 icolumnCollection = icol.InputColumnCollection;
//output
PipelineWrapper.IDTSOutputCollection100 ocolCollection = UnpackDecimal.OutputCollection;
PipelineWrapper.IDTSOutput100 ocol = ocolCollection[0];
PipelineWrapper.IDTSOutputColumnCollection100 ocolumnCollection = ocol.OutputColumnCollection;
foreach (DataRow row in Metadata.Rows)
{
int Scale = (int)row["Scale"];
String FieldLevelName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
if (Scale != -1)
{
foreach (PipelineWrapper.IDTSInputColumn100 input in icolumnCollection)
{
String InputColumnName = input.Name.ToString().Remove(0, 3);
if (input.DataType == RuntimeWrapper.DataType.DT_BYTES & InputColumnName.ToLower().Equals(FieldLevelName))
{
input.CustomPropertyCollection["PackedScale"].Value = Scale;
foreach (PipelineWrapper.IDTSOutputColumn100 output in ocolumnCollection)
{
int InputColumnId = (int)output.CustomPropertyCollection["InputColumnID"].Value;
if (InputColumnId == input.ID)
{
output.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DECIMAL, 0, 0, Scale, 0);
output.Name = FieldLevelName.ToLower();
}
}
}
}
}
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal = UnpackDecimal.Instantiate();
//Reinitiaze(cmwUnpackDecimal);
}
}
private void SetFlatFileProperty(PipelineWrapper.IDTSComponentMetaData100 FileSource)
{
PipelineWrapper.IDTSOutputCollection100 ocols1 = FileSource.OutputCollection;
PipelineWrapper.IDTSOutput100 ocols = ocols1[0];
foreach (PipelineWrapper.IDTSOutputColumn100 col in ocols.OutputColumnCollection)
{
RuntimeWrapper.DataType dty = col.DataType;
if (dty == RuntimeWrapper.DataType.DT_BYTES)
{
col.CustomPropertyCollection["UseBinaryFormat"].Value = true;
}
}
}
/*
* Add Destinatin Componnet
*/
private PipelineWrapper.IDTSComponentMetaData100 AddComponentMetadata(PipelineWrapper.MainPipe wf, String ComponentName)
{
PipelineWrapper.IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
return icmd;
}
private void SaveSSIS(String Directory, String PackageName)
{
String FullFilePath = Directory + "\\" + PackageName + ".dtsx";
myApplication.SaveToXml(FullFilePath, myPackage, null);
}
/* Read object Columns into a datatable*/
private DataTable ReadColumnInfo(object ColumnInfo)
{
OleDbDataAdapter oleDb = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDb.Fill(dt, ColumnInfo);
return dt;
}
/*
* Reinitiazied
*/
private void Reinitiaze(PipelineWrapper.CManagedComponentWrapper InstanceSource)
{
//Reinitialize Flat File source metadata,
InstanceSource.AcquireConnections(null);
InstanceSource.ReinitializeMetaData();
InstanceSource.ReleaseConnections();
}
/*
* Add OLEDB Connection
*/
public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
{
ConnectionManager Destination = myPackage.Connections.Add("OLEDB");
Destination.ConnectionString = ConnectionStr + "Packet Size=32076;";
Destination.Name = ConnectionName;
Destination.Description = "SQL OLEDB using " + ConnectionName;
return Destination;
}
/*
* Add WorkFlow
*
*/
private PipelineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
{
myPackage.Executables.Add("STOCK:PipelineTask");
TaskHost _TaskHost = (TaskHost)myPackage.Executables[0];
PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
_TaskHost.Name = ComponentName;
_TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");
return dataFlowTask;
}
/*
* Add File Source Componnet
*
*/
private PipelineWrapper.IDTSComponentMetaData100 AddFileSourceComponent(PipelineWrapper.MainPipe dataFlowTask, ConnectionManager flaFileCon, String ComponentName)
{
PipelineWrapper.IDTSComponentMetaData100 FileSource = dataFlowTask.ComponentMetaDataCollection.New();
FileSource.Name = "SourceFile" + ComponentName;
FileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
PipelineWrapper.CManagedComponentWrapper instanceFileSource = FileSource.Instantiate();
instanceFileSource.ProvideComponentProperties();
//Set source connection
FileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flaFileCon.ID;
FileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flaFileCon);
//Reinitialize Flat File source metadata,
Reinitiaze(instanceFileSource);
return FileSource;
}
//Generate Insert command
public void GenerateInsert(DataTable dtColumns)
{
Load2MasterCmd.Append(" Select " + Environment.NewLine);
Load2MasterCmd.Append(BDELoaderId.ToString() + " As BDELoaderID,");
Master2Oracle.Append(" Select " + Environment.NewLine);
Master2Oracle.Append("[MasterId],");
foreach (DataRow row in dtColumns.Rows)
{
int FieldLocation = (int)row["FieldLocation"];
String ColName = (String)row["MasterColumnINfo"];
String OraCol = (String)row["OracleColumnInfo"];
switch (FieldLocation)
{
case 1:
Load2MasterCmd.Append(ColName.ToString());
Load2MasterCmd.Append(Environment.NewLine);
Master2Oracle.Append(OraCol.ToString());
Master2Oracle.Append(Environment.NewLine);
break;
default:
Load2MasterCmd.Append("," + ColName.ToString());
Load2MasterCmd.Append(Environment.NewLine);
Master2Oracle.Append("," + OraCol.ToString());
Master2Oracle.Append(Environment.NewLine);
break;
}
}
Load2MasterCmd.Append(" From Load." + vars["User::strTableName"].Value.ToString());
Master2Oracle.Append(" From Master." + vars["User::strTableName"].Value.ToString());
}
//Generate Insert From Load to Master
/*
* Create Fixed Width Column Connection Manager
*/
private ConnectionManager AddFixedWith(DataTable dtColumns, String ConnectionName, String ConnectionString)
{
String DeltaFileByte = "DELTA_FILE_BYTE";
Boolean UsedDeltaByte = (Boolean)vars["User::bolUseDeltaByte"].Value;
//Create Insert Statement From LoadToMaster
//StringBuilder LoadMasterCmd = new StringBuilder();
//LoadMasterCmd.Append("INSERT INTO [Master].");
//LoadMasterCmd.Append(vars["User::strTableName"].Value.ToString() + "(" + Environment.NewLine);
ConnectionManager flatConn = myPackage.Connections.Add("FLATFILE");
flatConn.Name = ConnectionName.Replace(" ","");
flatConn.ConnectionString = ConnectionString;
flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
flatConn.Properties["CodePage"].SetValue(flatConn, "37");
flatConn.Properties["DataRowsToSkip"].SetValue(flatConn,0);
flatConn.Properties["RowDelimiter"].SetValue(flatConn, "\r\n");
flatConn.Properties["LocaleID"].SetValue(flatConn, "1033");
//Inner object
RuntimeWrapper.IDTSConnectionManagerFlatFile100 ConnectionFlatFile = (RuntimeWrapper.IDTSConnectionManagerFlatFile100)flatConn.InnerObject;
foreach (DataRow row in dtColumns.Rows)
{
String ColumnName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
if (UsedDeltaByte)
{
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
FlatFileColumn100.ColumnType = "FixedWidth";
FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
RuntimeWrapper.IDTSName100 ColName = (RuntimeWrapper.IDTSName100)FlatFileColumn100;
int FieldLocation = (int)row["FieldLocation"];
int Scale = (int)row["Scale"];
switch (Scale)
{
case -1:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
break;
default:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
break;
}
}
else
{
if (!DeltaFileByte.Equals(ColumnName))
{
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
FlatFileColumn100.ColumnType = "FixedWidth";
FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
RuntimeWrapper.IDTSName100 ColName = (RuntimeWrapper.IDTSName100)FlatFileColumn100;
int FieldLocation = (int)row["FieldLocation"];
int Scale = (int)row["Scale"];
switch (Scale)
{
case -1:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
break;
default:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
break;
}
}
}
}
//LoadMasterCmd.Append(")");
//Load2MasterCmd = LoadMasterCmd;
//LoadMasterCmd = null;
return flatConn;
}
/*
*
* Unpack Decimal
*/
private PipelineWrapper.IDTSComponentMetaData100 AddUnpackDecimal(PipelineWrapper.MainPipe dataFlowTask, String Name)
{
PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();
// mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
mpk.ComponentClassID = typeof(Comp3).AssemblyQualifiedName;
mpk.Name = Name;
PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
instance.ProvideComponentProperties();
// Reinitiaze(instance);
return mpk;
}
/*
*
* Unpack integers
*/
private PipelineWrapper.IDTSComponentMetaData100 AddUnpackIntegers(PipelineWrapper.MainPipe dataFlowTask, String Name)
{
PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();
// mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
mpk.ComponentClassID = typeof(Comp).AssemblyQualifiedName;
PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
instance.ProvideComponentProperties();
mpk.Name = Name;
// Reinitiaze(instance);
return mpk;
}
private PipelineWrapper.IDTSComponentMetaData100 AddTrimThatColumn(PipelineWrapper.MainPipe dataFlowTask, String Name)
{
PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();
// mpk.ComponentClassID = typeof(CustomComponents.TrimThatColumn).AssemblyQualifiedName;
mpk.ComponentClassID = typeof(TrimThatColumn).AssemblyQualifiedName;
PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
instance.ProvideComponentProperties();
mpk.Name = Name;
// Reinitiaze(instance);
return mpk;
}
// Create SSIS executable
private Executable AddSQLExecutable(String MONIKER)
{
Executable exec = myPackage.Executables.Add(MONIKER);
return exec;
}
// Select Decimals Input and Generate Output
private void SelectDecimalColumns(PipelineWrapper.IDTSComponentMetaData100 PackedDecimal,DataTable Metadata,String CompactType)
{
PipelineWrapper.CManagedComponentWrapper InstanceUnpack = PackedDecimal.Instantiate();
PipelineWrapper.IDTSInputCollection100 inputCollection = PackedDecimal.InputCollection;
PipelineWrapper.IDTSInput100 input = inputCollection[0];
PipelineWrapper.IDTSVirtualInput100 vinput = input.GetVirtualInput();
PipelineWrapper.IDTSVirtualInputColumnCollection100 vInputColumns = vinput.VirtualInputColumnCollection;
foreach (DataRow row in Metadata.Rows)
{
Boolean bolComp = (Boolean)row[CompactType];
String FieldLevelName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
if(bolComp)
{
foreach (PipelineWrapper.IDTSVirtualInputColumn100 virtualInputColumn in vInputColumns)
{
String InputColumnName = virtualInputColumn.Name.ToString().ToLower();
if (virtualInputColumn.DataType == RuntimeWrapper.DataType.DT_BYTES & InputColumnName.ToLower().Equals(FieldLevelName))
{
// Select column, and retain new input column
PipelineWrapper.IDTSInputColumn100 inputColumn = InstanceUnpack.SetUsageType(input.ID, vinput, virtualInputColumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
}
}
}
}
}
private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd)
{
Microsoft.SqlServer.Dts.Runtime.TaskHost sqlTaskHost = (Microsoft.SqlServer.Dts.Runtime.TaskHost)exec;
//Add Properties
sqlTaskHost.Properties["Name"].SetValue(sqlTaskHost, TaskName);
sqlTaskHost.Properties["Description"].SetValue(sqlTaskHost, TaskName);
sqlTaskHost.Properties["Connection"].SetValue(sqlTaskHost, Connection);
sqlTaskHost.Properties["SqlStatementSource"].SetValue(sqlTaskHost, SqlCmd);
IDTSExecuteSQL iexecSqlTask = (IDTSExecuteSQL)sqlTaskHost.InnerObject;
iexecSqlTask.ResultSetType = ResultSetType.ResultSetType_None;
iexecSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
}
private void MappColumns(PipelineWrapper.IDTSComponentMetaData100 icmDest, PipelineWrapper.CManagedComponentWrapper mcwDest)
{
//mappings
Dictionary<String, int> extenalColumnLineageId = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase);
PipelineWrapper.IDTSInput100 inpDestination = icmDest.InputCollection[0];
PipelineWrapper.IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = (PipelineWrapper.IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;
foreach(PipelineWrapper.IDTSExternalMetadataColumn100 extcol in inpDestination.ExternalMetadataColumnCollection)
{
extenalColumnLineageId.Add(extcol.Name, extcol.ID);
}
foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
{
try
{
if (vcolumn.DataType != RuntimeWrapper.DataType.DT_BYTES && extenalColumnLineageId.ContainsKey(vcolumn.Name))
{
int extcolId = extenalColumnLineageId[vcolumn.Name.ToString()];
int destinationId = inpDestination.ID;
int lineageId = vcolumn.LineageID;
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(destinationId, vinpDestination, lineageId, PipelineWrapper.DTSUsageType.UT_READONLY);
// map external column
// inputColumn.Name = inputColumn.Name.Replace("[", "").Replace("]", "");
PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = (PipelineWrapper.IDTSExternalMetadataColumn100)inpDestination.ExternalMetadataColumnCollection.FindObjectByID(extcolId);
mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
}
else
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
}
}
catch (Exception e)
{
String error = e.Message.ToString();
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
// map external column
}
}
if (mcwDest.Validate() == PipelineWrapper.DTSValidationStatus.VS_NEEDSNEWMETADATA)
{
mcwDest.ReinitializeMetaData();
}
}
private void MappColumnsLookup(PipelineWrapper.IDTSComponentMetaData100 icmLookup, PipelineWrapper.CManagedComponentWrapper mcwLookup, string DeltaColumns)
{
//mappings
PipelineWrapper.IDTSInput100 inputLookup = icmLookup.InputCollection[0];
PipelineWrapper.IDTSInputColumnCollection100 cinputlookup = inputLookup.InputColumnCollection;
PipelineWrapper.IDTSVirtualInput100 vinputLookup = inputLookup.GetVirtualInput();
PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = vinputLookup.VirtualInputColumnCollection;
foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinputLookup.VirtualInputColumnCollection)
{
try
{
String ColumnName = vcolumn.Name.ToString();
if (String.Compare(ColumnName,DeltaColumns,true)==0)
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
// map external column
mcwLookup.SetInputColumnProperty(inputLookup.ID, inputColumn.ID, "JoinToReferenceColumn", vcolumn.Name);
}
else
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
}
// mcwLookup.MapInputColumn(inputLookup.ID, inputColumn.ID, extColumn.ID);
}
catch
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
throw;
// map external column
}
}
}
#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
}
}
No comments:
Post a Comment