After a constant change and continue the development of this new process
here is the base line used to convert comp3 fields.
The link to the component is below:
http://www.microsoft.com/en-us/download/details.aspx?id=20397
however I did add the changes to the component according to what was missing and upgraded the code to MS SQL 2012 :-)
#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.UnpackDecimalComponent;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using ScriptTask = Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using Microsoft.CSharp.RuntimeBinder;
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();
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");
//Get variable collector
Dts.VariableDispenser.GetVariables(ref vars);
BDELoaderId = (int)vars["User::intBDELoaderID"].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);
//Truncate table Command Variable
StringBuilder TruncateCommand = new StringBuilder();
TruncateCommand.Append(vars["User::strSqlCmd"].Value.ToString() + " ");
TruncateCommand.Append("[Load]." + vars["User::strTableName"].Value.ToString());
// Set Package Name
myPackage.Name = "SssisLoad2Master" + vars["User::strTabName"].Value.ToString();
String LoadDirectory = vars["User::strLoadDirectory"].Value.ToString();
SaveDirectory = vars["User::strSaveDirectory"].Value.ToString();
String LoadTableName = "[Load]." + vars["User::strTableName"].Value.ToString();
String FileName = vars["User::strFileName"].Value.ToString();
String FlatFileConnectionString = LoadDirectory + "\\" + FileName;
String TabName = vars["User::strTabName"].Value.ToString();
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 SourceToCount;
PipelineWrapper.IDTSPath100 CountToDecimal;
PipelineWrapper.IDTSPath100 DecimalToDestination;
// 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(), FlatFileConnectionString);
GenerateInsert(MasterColumnInfo);
String MasterToOracle = Master2Oracle.ToString();
//Create OLEDB Connection
ConnectionManager ConMgr = AddOLEDBConnection("destination", Dts.Connections["host"].ConnectionString.ToString());
ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].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
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);
//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 Unpack Decimal Custom Component
PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal = AddUnpackDecimal(DataFlow);
UnpackDecimal.Name = "UnPackComp3Fields";
PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal = UnpackDecimal.Instantiate();
cmwUnpackDecimal.ProvideComponentProperties();
//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", 0);//FastLoad
mcwDestination.SetComponentProperty("OpenRowset", LoadTableName);//tblDestination);
mcwDestination.Validate();
mcwDestination.SetComponentProperty("FastLoadKeepIdentity", true);
mcwDestination.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
mcwDestination.SetComponentProperty("FastLoadKeepNulls", false);
mcwDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
Reinitiaze(mcwDestination);
//Join Source To UnpackDecimal Component
SourceToCount = DataFlow.PathCollection.New();
SourceToCount.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
CountToDecimal = DataFlow.PathCollection.New();
CountToDecimal.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], UnpackDecimal.InputCollection[0]);
//Set Properties for Bynary Fields
SetFlatFileProperty(FileSource);
SelectDecimalColumns(UnpackDecimal);
SetUnpackDecimalScale(UnpackDecimal, ColumnInfo);
//Join Unpack Decimal to Destination
DecimalToDestination = DataFlow.PathCollection.New();
DecimalToDestination.AttachPathAndPropagateNotifications(UnpackDecimal.OutputCollection[0], icmDestination.InputCollection[0]);
MappColumns(icmDestination, mcwDestination);
// myPackage.Execute();
vars["User::intRecordCount"].Value = intFileRowCount.Value;
if ((myPackage.ExecutionResult == 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());
// TODO: Add your code here
vars.Unlock();
//ColumnInfo = null;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
catch
{
myPackage.Name = "Error" + myPackage.Name.ToString();
SaveSSIS(SaveDirectory, myPackage.Name.ToString());
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"];
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.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;
}
}
}
}
}
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;
col.Name = "pkg" + col.Name.ToString();
}
}
}
/*
* 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)
{
//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;
flatConn.ConnectionString = ConnectionString;
flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
flatConn.Properties["CodePage"].SetValue(flatConn, "37");
flatConn.Properties["DataRowsToSkip"].SetValue(flatConn, 1);
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)
{
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"];
switch (FieldLocation)
{
case 1:
LoadMasterCmd.Append("[BDELoaderId],[" + (String)row["FieldLevelName"] + "]");
Load2MasterCmd.Append(Environment.NewLine);
break;
default:
LoadMasterCmd.Append(",[" + (String)row["FieldLevelName"] + "]");
Load2MasterCmd.Append(Environment.NewLine);
break;
}
int Scale = (int)row["Scale"];
switch (Scale)
{
case -1:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
ColName.Name = (String)row["FieldLevelName"];
break;
default:
FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
ColName.Name = (String)row["FieldLevelName"];
break;
}
}
LoadMasterCmd.Append(")");
Load2MasterCmd = LoadMasterCmd;
LoadMasterCmd = null;
return flatConn;
}
/*
*
* Unpack Decimal
*/
private PipelineWrapper.IDTSComponentMetaData100 AddUnpackDecimal(PipelineWrapper.MainPipe dataFlowTask)
{
PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();
// mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
mpk.ComponentClassID = typeof(Comp3).AssemblyQualifiedName;
mpk.Name = "Comp3PkgFields";
PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
instance.ProvideComponentProperties();
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)
{
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 (PipelineWrapper.IDTSVirtualInputColumn100 virtualInputColumn in vInputColumns)
{
if (virtualInputColumn.DataType == RuntimeWrapper.DataType.DT_BYTES)
{
// 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
PipelineWrapper.IDTSInput100 inpDestination = icmDest.InputCollection[0];
PipelineWrapper.IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = (PipelineWrapper.IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;
foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
{
try
{
if (vcolumn.DataType != RuntimeWrapper.DataType.DT_BYTES)
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
// map external column
PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
}
else
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
}
}
catch
{
PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
// 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
}
}
