ETL Developer Notes (すべてがデータです)

September 16, 2014

Loading Comp-3 and Comp from MainFrame using SSIS Programmatically

The below code will load MainFrame files in IBM-37 transforming comp-3 and comp fields using custom components.

#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 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");
//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);

//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(" ","");

// 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 TrimmerToDestination;

// 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 ConMgr = 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;
snapshotDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
snapshotDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
PipelineWrapper.IDTSCustomProperty100 outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
outSnapShotDate.Name = "Expression";
outSnapShotDate.Value = "@[User::dtSnapshotDate]";
outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
outSnapShotDate.Name = "FriendlyExpression";
outSnapShotDate.Value = "@[User::dtSnapshotDate]";

//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();

// 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 = ConMgr.ID;
icmDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConMgr);
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]);

TrimmerToDestination = DataFlow.PathCollection.New();
TrimmerToDestination.AttachPathAndPropagateNotifications(TrimThatColumn0.OutputCollection[0],icmDestination.InputCollection[0] );
// Mapping externalcolumns Array

MappColumns(icmDestination, mcwDestination);

// SaveSSIS(@"E:\\BDELoader\\ssis\\", myPackage.Name.ToString());
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 ConMgr = myPackage.Connections.Add("OLEDB");

ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
ConMgr.Name = ConnectionName;
ConMgr.Description = "SQL OLEDB using " + ConnectionName;

return ConMgr;

}
/*
* 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();
}

}

#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

}
}

Codeplex 2012 Unpack decimal

//UnpackDecimal
//===========

//SSIS has no provision for converting packed decimal (comp-3) data.

//This transform takes a bytes column, and converts to decimal, using a user provided scale

//Configure in advanced editor by clicking input columns containing packed decimal values.
//Afterwards, go to the input and output tab and set the PackedScale property on the input column
//if it is to differ from 0.

//This component automatically creates an output column when the input column is selected, then
//forbids any change to that column's metadata. The only allowed user edits are on column name and
//description, and the scale. All else are rejected.

//Scale must be between 0 and 28.
//The length of the input field must be 14 bytes or fewer. This imposes a limit of 27 digits on the
//converted value. Though 28 digit numbers are supported by the decimal format, they take 15 bytes
//to store, and a 15 byte packed can hold 29 digits, overflowing the decimal at runtime.

//Left as an exercise for the student:
//a) add an error output to direct overflows or badly formatted value to.
//b) update component to allow 15 bytes in, and reject values with non-zero most significant nibble.

//Interesting Features
//====================

//This component is part of a series of components that illustrate increasingly complex
//behavior, each one exercising a greater proportion of the SSIS object model. If studying
//in order, this component follows ConfigureUnDouble, and precedes UnDoubleOut.

//This component was built to provide an introduction to the use of output columns. Also
//illustrated are:

//- Binding input to output columns with custom properties.
//- Distinguishing upstream columns from each other.
//- Operating on DT_BYTES
//- Copy and paste support
//- Use of Ondeletinginputcolumn.
//- Setusagetype gives you a virtual input. (because new columns won’t be in the input)
//- ReinitializeMetadata to clear up referring columns
//- Use the input buffer id not the output buffer id, when setting output column values.

// By James Howey
// Copyright (c) Microsoft Corporation. All rights reserved.

using System;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts;

using System.Diagnostics;

namespace CustomComponents
{
[DtsPipelineComponent(
DisplayName = "Unpack Decimal",
ComponentType = ComponentType.Transform,
IconResource = "UnpackDecimal.UnpackDecimal.ico")]

public class UnpackDecimal : PipelineComponent
{
#region helper methods and objects

private void PostError(string message)
{
bool cancel = false;
this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, message, "", 0, out cancel);
}

private DTSValidationStatus promoteStatus(ref DTSValidationStatus currentStatus, DTSValidationStatus newStatus)
{
// statuses are ranked in order of increasing severity, from
// valid to broken to needsnewmetadata to corrupt.
// bad status, if any, is result of programming error
switch (currentStatus)
{
case DTSValidationStatus.VS_ISVALID:
switch (newStatus)
{
case DTSValidationStatus.VS_ISBROKEN:
case DTSValidationStatus.VS_ISCORRUPT:
case DTSValidationStatus.VS_NEEDSNEWMETADATA:
currentStatus = newStatus;
break;
case DTSValidationStatus.VS_ISVALID:
break;
default:
throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
}
break;
case DTSValidationStatus.VS_ISBROKEN:
switch (newStatus)
{
case DTSValidationStatus.VS_ISCORRUPT:
case DTSValidationStatus.VS_NEEDSNEWMETADATA:
currentStatus = newStatus;
break;
case DTSValidationStatus.VS_ISVALID:
case DTSValidationStatus.VS_ISBROKEN:
break;
default:
throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
}
break;
case DTSValidationStatus.VS_NEEDSNEWMETADATA:
switch (newStatus)
{
case DTSValidationStatus.VS_ISCORRUPT:
currentStatus = newStatus;
break;
case DTSValidationStatus.VS_ISVALID:
case DTSValidationStatus.VS_ISBROKEN:
case DTSValidationStatus.VS_NEEDSNEWMETADATA:
break;
default:
throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
}
break;
case DTSValidationStatus.VS_ISCORRUPT:
switch (newStatus)
{
case DTSValidationStatus.VS_ISCORRUPT:
case DTSValidationStatus.VS_ISVALID:
case DTSValidationStatus.VS_ISBROKEN:
case DTSValidationStatus.VS_NEEDSNEWMETADATA:
break;
default:
throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
}
break;
default:
throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
}
return currentStatus;
}
#endregion

#region design time functionality

//public override void ProvideComponentProperties()
//{
// this.RemoveAllInputsOutputsAndCustomProperties();
// base.ProvideComponentProperties();

// this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
// // Specify that the Component has an error output.
// this.ComponentMetaData.UsesDispositions = true;

// this.ComponentMetaData.Name = "Unpack Decimal";
// this.ComponentMetaData.Description = "SSIS Data Flow Component to convert Comp-3 fields into standard numeric fields";
// this.ComponentMetaData.ContactInfo = "ETLDEVDBA@gmail.com";

// // Input
// IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
// input.Name = "Input";
// input.HasSideEffects = false;
// input.Description = "Compact Decimal Binary Stream inputs";
// input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
// input.ErrorOrTruncationOperation = "ValidationFailure";

// //Output
// IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
// output.Name = "Output";
// output.IsErrorOut = false;
// output.HasSideEffects = false;
// output.SynchronousInputID = input.ID;
// output.ExclusionGroup = 1;

//}
public override DTSValidationStatus Validate()
{
// if scale doesn't match output column, or extra output columns, or insufficient
// output columns, this component is corrupt. We can say this because in our design
// time methods, we have compelled the output columns to remain in lockstep with
// input column selections

// if I had numerics, I would have to worry about matching the precision of the numeric
// to the length of the packed field, but decimal is fixed length, so all that changes
// is scale.

// check for:
// basic layout validation
// orphaned input columns

// if component is corrupt, we are permitted to return without further checks
DTSValidationStatus status = base.Validate();
if (status == DTSValidationStatus.VS_ISCORRUPT)
{
return status;
}

IDTSComponentMetaData100 metadata = this.ComponentMetaData;
IDTSCustomProperty100 customProperty;
int lineageID;

IDTSInputCollection100 inputCollection = metadata.InputCollection;
IDTSInput100 input = inputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
IDTSInputColumn100 inputColumn;

IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSOutputColumn100 outputColumn;

if (inputColumnCollection.Count != outputColumnCollection.Count)
{
PostError("Input and output columns don't match up.");
return DTSValidationStatus.VS_ISCORRUPT;
}

for (int j = 0; j < outputColumnCollection.Count; j++)
{
outputColumn = outputColumnCollection[j];
lineageID = outputColumn.LineageID;
IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
try
{
customProperty = customPropertyCollection["InputColumnID"];
}
catch (Exception)
{
PostError("Output column " + outputColumn.Name + " has no InputColumnID custom property.");
return DTSValidationStatus.VS_ISCORRUPT;
}

int inputColumnID = (int)customProperty.Value;
try
{
inputColumn = inputColumnCollection.FindObjectByID(inputColumnID);
}
catch (Exception)
{
PostError("InputColumnID " + inputColumnID + " not found in selected input columns.");
return DTSValidationStatus.VS_ISCORRUPT;
}
// if input column is orphaned, we already have reinit status, and cleanup will
// eliminate any following errors.
if (inputColumn.IsValid)
{
if (inputColumn.DataType != DataType.DT_BYTES)
{
PostError("Column " + inputColumn.Name + " is not a DT_BYTES column");
promoteStatus(ref status, DTSValidationStatus.VS_ISBROKEN);
}
IDTSCustomPropertyCollection100 inputColumnCustomProperties = inputColumn.CustomPropertyCollection;
try
{
customProperty = inputColumnCustomProperties["PackedScale"];
}
catch (Exception)
{
PostError("InputColumnID " + inputColumnID + " has no PackedScale property");
return DTSValidationStatus.VS_ISCORRUPT;
}
int packedScale = (int)customProperty.Value;
if (packedScale < 0 || packedScale > 28)
{
PostError("PackedScale must be between 0 and 28.");
return DTSValidationStatus.VS_ISCORRUPT;
}
if (outputColumn.DataType != DataType.DT_DECIMAL)
{
PostError("Output column data type must be decimal");
return DTSValidationStatus.VS_ISCORRUPT;
}
if (outputColumn.Scale != packedScale)
{

PostError("PackedScale must match output column scale.");
return DTSValidationStatus.VS_ISCORRUPT;
}
}
}
return status;
}

public override void ReinitializeMetaData()
{
// This should delete orphaned columns for us, calling OnDeletingInputColumn
base.ReinitializeMetaData();
}

public override IDTSInput100 InsertInput(DTSInsertPlacement insertPlacement, int inputID)
{
PostError("Component requires exactly one input. New input is forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDINPUT);
}

public override void DeleteInput(int inputID)
{
PostError("Component requires exactly one input. Deleted input is forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTDELETEINPUT);
}

public override IDTSOutput100 InsertOutput(DTSInsertPlacement insertPlacement, int outputID)
{
PostError("Component requires exactly one output. New output is forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDOUTPUT);
}

public override void DeleteOutput(int outputID)
{
PostError("Component requires exactly one output. Deleted output is forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTDELETEOUTPUT);
}

public override IDTSOutputColumn100 InsertOutputColumnAt(int outputID, int outputColumnIndex, string name, string description)
{
PostError("Component forbids adding output columns. Check input columnn to configure.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDCOLUMN);
}

public override void DeleteOutputColumn(int outputID, int outputColumnID)
{
PostError("Component forbids deleting output columns. Uncheck input columnn to configure.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDCOLUMN);
}

// done
public override void OnDeletingInputColumn(int inputID, int inputColumnID)
{
IDTSComponentMetaData100 metadata = this.ComponentMetaData;

// An input column is being deleted. This may be because it no longer is
// present upstream, or because the user has unchecked it.
// We have to retrieve the column, then remove any
// reference to it in the output column collection
IDTSInputCollection100 inputCollection = metadata.InputCollection;
IDTSInput100 input = inputCollection[0];
// Get input column collection and retrieve interesting column
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
IDTSInputColumn100 inputColumn = inputColumnCollection.FindObjectByID(inputColumnID);

// Iterate over output columns looking for matching id in custom property.
IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
for (int j = 0; j < outputColumnCollection.Count; j++)
{
IDTSOutputColumn100 outputColumn = outputColumnCollection[j];
IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
int columnID = (int)customProperty.Value;
if (columnID == inputColumnID)
{
// we just delete the output column
base.DeleteOutputColumn(output.ID, outputColumn.ID);
}
}
}

// done

public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn;
IDTSComponentMetaData100 metadata = this.ComponentMetaData;

switch (usageType)
{
case DTSUsageType.UT_READONLY:
IDTSVirtualInputColumn100 column = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
if (column.DataType != DataType.DT_BYTES)
{
PostError("Component operates only on bytes input. Other types are forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
else
{
if (column.Length > 14)
{
PostError("Component accepts a maximum field length of 14.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);

String ColumnName = inputColumn.Name.ToString();
String oColumnName = "";
if (ColumnName.Substring(0, 2).Equals("pkg"))
{
inputColumn.Name = ColumnName;
oColumnName = ColumnName.Replace("pkg", "");
}
else
{
inputColumn.Name = "pkg" + ColumnName;
oColumnName = ColumnName;

}

IDTSCustomPropertyCollection100 customProperties = inputColumn.CustomPropertyCollection;
IDTSCustomProperty100 customProperty = customProperties.New();
customProperty.Name = "PackedScale"; // do not localize
customProperty.ContainsID = false;
customProperty.Value = 0; // default is zero for scale

IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
// this will generate a unique name, because upstream component names can't have dots int them
//IDTSOutputColumn100 newColumn = base.InsertOutputColumnAt(output.ID, outputColumnCollection.Count,
// inputColumn.UpstreamComponentName + "." + inputColumn.Name + ".Decimal", "");
IDTSOutputColumn100 newColumn = base.InsertOutputColumnAt(output.ID, outputColumnCollection.Count,
oColumnName, "UnpackColumn");
newColumn.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, 0, 0);

customProperties = newColumn.CustomPropertyCollection;
customProperty = customProperties.New();
customProperty.Name = "InputColumnID"; // do not localize
// support cut and paste
customProperty.ContainsID = true;
customProperty.Value = inputColumn.ID;

return inputColumn;
}
}
case DTSUsageType.UT_READWRITE:
PostError("Component requires that input columns be marked read only.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
case DTSUsageType.UT_IGNORED:
IDTSInputCollection100 inputCollection = metadata.InputCollection;
IDTSInput100 input = inputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
this.OnDeletingInputColumn(inputID, inputColumn.ID);
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
return inputColumn;
default:
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
}
public override IDTSCustomProperty100 SetOutputColumnProperty(int outputID, int outputColumnID, string propertyName, object propertyValue)
{
if (propertyName == "Scale")
{
int value = (int)propertyValue;
// scale ranges from 0 to 28
if (value >= 0 && value <= 28)
{
IDTSComponentMetaData100 metadata = this.ComponentMetaData;
//IDTSOutputCollection100 OutputCollection = metadata.OutputCollection;
//IDTSOutput100 Output = OutputCollection[0];
//IDTSOutputColumnCollection100 OutputColumnCollection = Output.OutputColumnCollection;
//IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
//IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
return base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue);
}
else
{
PostError("PackedScale must be between 0 and 28.");
throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
}
}
else
{
PostError("Unexpected property name to set.");
throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
}

}

public override IDTSCustomProperty100 SetInputColumnProperty(int inputID, int inputColumnID, string propertyName, object propertyValue)
{
if (propertyName == "PackedScale")
{
int value = (int)propertyValue;
// scale ranges from 0 to 28
if (value >= 0 && value <= 28)
{
IDTSComponentMetaData100 metadata = this.ComponentMetaData;
IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSOutputColumn100 outputColumn;
for (int j = 0; j < outputColumnCollection.Count; j++)
{
outputColumn = outputColumnCollection[j];
IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
int linkedInputID = (int)customProperty.Value;
if (linkedInputID == inputColumnID)
{
outputColumn.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, value, 0);
return base.SetInputColumnProperty(inputID, inputColumnID, propertyName, propertyValue);
}
}
PostError("Couldn't find matching output column. Component likely corrupt.");
throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
}
else
{
PostError("PackedScale must be between 0 and 28.");
throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
}
}
else
{
PostError("Unexpected property name to set.");
throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
}
}

#endregion

#region private member variables

private int[] outColumnWriteIDs;
private int[] outColumnSourceIDs;
private int[] outColumnScale;
private int numOutColumnWrites = 0;

#endregion

#region runtime functionality
public override void PreExecute()
{
IDTSCustomProperty100 customProperty;
int lineageID;

IDTSComponentMetaData100 metadata = this.ComponentMetaData;

IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
int inputBufferID = input.Buffer;
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
IDTSInputColumn100 inputColumn;
IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSOutputColumn100 outputColumn;

// get output columns to write
this.outColumnWriteIDs = new int[outputColumnCollection.Count];
this.outColumnSourceIDs = new int[outputColumnCollection.Count];
this.outColumnScale = new int[outputColumnCollection.Count];
this.numOutColumnWrites = 0;
for (int j = 0; j < outputColumnCollection.Count; j++)
{
outputColumn = outputColumnCollection[j];
lineageID = outputColumn.LineageID;
// this.numOutColumnWrites index is incremented farther below.
this.outColumnWriteIDs[this.numOutColumnWrites] = this.BufferManager.FindColumnByLineageID(inputBufferID, lineageID);
IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
customProperty = customPropertyCollection["InputColumnID"];
int inputID = (int)customProperty.Value;
inputColumn = inputColumnCollection.FindObjectByID(inputID);
this.outColumnSourceIDs[this.numOutColumnWrites] = this.BufferManager.FindColumnByLineageID(inputBufferID, inputColumn.LineageID);
IDTSCustomPropertyCollection100 inputColumnCustomProperties = inputColumn.CustomPropertyCollection;
customProperty = inputColumnCustomProperties["PackedScale"];
this.outColumnScale[this.numOutColumnWrites++] = (int)customProperty.Value;

}
}

public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
System.Decimal result;

byte[] source;
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
// this component nulls output columns on badly formatted input data
// a better implementation would provide an error output and give
// user control over disposition on error.
for (int j = 0; j < this.numOutColumnWrites; j++)
{
try
{
// GetBytes will throw if source column is null
source = buffer.GetBytes(this.outColumnSourceIDs[j]);
result = Unpack(source, this.outColumnScale[j]);
buffer.SetDecimal(this.outColumnWriteIDs[j], result);
}
catch (Exception)
{
buffer.SetNull(this.outColumnWriteIDs[j]);
}
}
}
}
}

private Decimal Unpack(byte[] inp, int scale)
{
long lo = 0;
long mid = 0;
long hi = 0;
bool isNegative;

// this nybble stores only the sign, not a digit.
// "C" hex is positive, "D" hex is negative, and "F" hex is unsigned.
switch (nibble(inp, 0))
{
case 0x0D:
isNegative = true;
break;
case 0x0F:
case 0x0C:
isNegative = false;
break;
default:
throw new Exception("Bad sign nibble");
}
long intermediate;
long carry;
long digit;
for (int j = inp.Length * 2 - 1; j > 0; j--)
{
// multiply by 10
intermediate = lo * 10;
lo = intermediate & 0xffffffff;
carry = intermediate >> 32;
intermediate = mid * 10 + carry;
mid = intermediate & 0xffffffff;
carry = intermediate >> 32;
intermediate = hi * 10 + carry;
hi = intermediate & 0xffffffff;
carry = intermediate >> 32;
// By limiting input length to 14, we ensure overflow will never occur

digit = nibble(inp, j);
if (digit > 9)
{
throw new Exception("Bad digit");
}
intermediate = lo + digit;
lo = intermediate & 0xffffffff;
carry = intermediate >> 32;
if (carry > 0)
{
intermediate = mid + carry;
mid = intermediate & 0xffffffff;
carry = intermediate >> 32;
if (carry > 0)
{
intermediate = hi + carry;
hi = intermediate & 0xffffffff;
carry = intermediate >> 32;
// carry should never be non-zero. Back up with validation
}
}
}
return new Decimal((int)lo, (int)mid, (int)hi, isNegative, (byte)scale);
}

private int nibble(byte[] inp, int nibbleNo)
{
int b = inp[inp.Length - 1 - nibbleNo / 2];
return (nibbleNo % 2 == 0) ? (b & 0x0000000F) : (b >> 4);
}

#endregion

}
}

September 16, 2014

Loading Comp-3 and Comp from MainFrame using SSIS Programmatically

Codeplex 2012 Unpack decimal

Contact Form