this first pass is just to make it work, not changes in the original logic, however I have to said I have gone a long way from my early days of coding to today. The next step is to create a most robust package but for my future presentation,but I have to remember this was done sometime back in 2005 and it finally got decommission on 2011 it got the job done for many of the data refresh for the ops dba. Our current method is far superior in quality and performer but as always this is a good example for some poor ETL developer in some dark alley scratching his head on how to transfer all those 300+ tables across multiple despair environments..
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using System.IO;
using System.Text;
#endregion
namespace ST_191bde99d06c46c4a731611b77518191
{
/// <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>
//Global variable
Variables var = null;
Microsoft.SqlServer.Dts.Runtime.Package pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
public void Main()
{
//Read
Dts.VariableDispenser.LockForRead("User::strPkColumn");
Dts.VariableDispenser.LockForRead("User::strBatchId");
Dts.VariableDispenser.LockForRead("User::strSourceArchiveTable");
Dts.VariableDispenser.LockForRead("User::bitIsRetention");
Dts.VariableDispenser.LockForRead("User::intArchiveExportId");
Dts.VariableDispenser.LockForRead("User::intFastLoadBatchSize");
Dts.VariableDispenser.LockForRead("User::intFastLoadCommit");
Dts.VariableDispenser.LockForRead("User::bitIsFastLoad");
Dts.VariableDispenser.LockForRead("User::bitIsSqlCmd");
Dts.VariableDispenser.LockForRead("User::bitIsSqlOverride");
Dts.VariableDispenser.LockForRead("User::bitIsTransferData");
Dts.VariableDispenser.LockForRead("User::strAutoSqlCmd");
Dts.VariableDispenser.LockForRead("User::strDestinationDatabase");
Dts.VariableDispenser.LockForRead("User::strDestinationSchema");
Dts.VariableDispenser.LockForRead("User::strDestinationServer");
Dts.VariableDispenser.LockForRead("User::strDestinationTable");
Dts.VariableDispenser.LockForRead("User::strSourceDatabase");
Dts.VariableDispenser.LockForRead("User::strSourceSchema");
Dts.VariableDispenser.LockForRead("User::strSourceServer");
Dts.VariableDispenser.LockForRead("User::strSourceTable");
Dts.VariableDispenser.LockForRead("User::strSqlCmd");
Dts.VariableDispenser.LockForRead("User::strSqlFilter");
//Write
Dts.VariableDispenser.LockForWrite("User::intIsPkgFail");
Dts.VariableDispenser.LockForWrite("User::strXmlDoc");
Dts.VariableDispenser.LockForWrite("User::intDestCurrCnt");
Dts.VariableDispenser.LockForWrite("User::intDestPrevCnt");
Dts.VariableDispenser.LockForWrite("User::intSourceCnt");
Dts.VariableDispenser.GetVariables(ref var);
//local variables
Microsoft.SqlServer.Dts.Runtime.Variable DestPrevCnt = pkg.Variables.Add("intDestPrevCnt", false, "User", 0);
Microsoft.SqlServer.Dts.Runtime.Variable DestCurrCnt = pkg.Variables.Add("intDestCurrCnt", false, "User", 0);
Microsoft.SqlServer.Dts.Runtime.Variable SourceCnt = pkg.Variables.Add("intSourceCnt", false, "User", 0);
Microsoft.SqlServer.Dts.Runtime.Variable ArchiveId = pkg.Variables.Add("intArchiveExportId", false, "User", var["User::intArchiveExportId"].Value);
Microsoft.SqlServer.Dts.Runtime.Variable BatchId = pkg.Variables.Add("strBatchId", false, "User", var["User::strBatchId"].Value.ToString());
pkg.Variables.Add("ExtractDate", false, "User", DateTime.Now);
String pkColumn = var["User::strPkColumn"].Value.ToString();
String SqlCounts = "Select Count (1) From ";
String SqlCmdForId = "Select " + pkColumn + " From ";
String RowsPerBatch = "";
String ExecuteSqlCmd = "";
//Connection Information
String SourceConStr = Dts.Connections["source"].ConnectionString;
String DestinationConStr = Dts.Connections["destination"].ConnectionString;
String HostConStr = Dts.Connections["host"].ConnectionString;
String MsdbConStr = Dts.Connections["msdb"].ConnectionString;
String SourceSqlCmd = var["User::strSqlCmd"].Value.ToString();
String AutoSqlCmd = var["User::strAutoSqlCmd"].Value.ToString();
String SourceSqlFilter = var["User::strSqlFilter"].Value.ToString();
String PackageName = "SQLSaturdayArchive_";
//Integers
Int32 ArchiveExportId = 0;
Int32 FastLoadMaxCommitSize = 0;
Int32 Failure = -999;
//Int32 intDestPrev = 0;
//Int32 intDestCurrCnt = 0;
//Int32 intSourceCnt = 0;
Microsoft.SqlServer.Dts.Runtime.TaskHost DestCnt;
Microsoft.SqlServer.Dts.Runtime.TaskHost DestCurrt;
Microsoft.SqlServer.Dts.Runtime.TaskHost DataFlowTaskHost;
IDTSComponentMetaData100 SourceComponent;
IDTSComponentMetaData100 SourceRowCount;
IDTSComponentMetaData100 DestinationComponent;
IDTSComponentMetaData100 MulticastComponent;
CManagedComponentWrapper SourceInstance;
CManagedComponentWrapper rowInstance;
CManagedComponentWrapper DestinationInstance;
CManagedComponentWrapper MulticastInstance;
IDTSPath100 SourceCntPath;
IDTSPath100 SourceMultiCastPath;
//IDTSPath100 DeriveColumnMulticastPath;
IDTSPath100 DestinationPath;
MainPipe DataFlowTask;
IDTSResultBinding DestcurrRB;
IDTSResultBinding DestRB;
ExecuteSQLTask DestCurr1;
ExecuteSQLTask DestCnt1;
Executable DataFlow;
LogProvider pkgLogging;
Executable seg;
Executable eDestCnt;
Executable eDestCurr;
Boolean IsFastLoad;
Boolean IsOverride;
Boolean IsQuery;
Boolean IsTransferData;
Boolean IsRetention;
Microsoft.SqlServer.Dts.Runtime.Sequence seg1;
Microsoft.SqlServer.Dts.Runtime.Sequence seg2;
Microsoft.SqlServer.Dts.Runtime.Sequence seg3;
Microsoft.SqlServer.Dts.Runtime.Sequence seg4;
//Source Variables
String SourceSchema = var["User::strSourceSchema"].Value.ToString();
String SourceTable = var["User::strSourceTable"].Value.ToString();
String SourceSchTable = "[" + var["User::strSourceDatabase"].Value.ToString() + "]." + "[" + SourceSchema + "].[" + SourceTable + "]";
String SourceServer = "[" + var["User::strSourceServer"].Value.ToString() + "]";
Microsoft.SqlServer.Dts.Runtime.Variable TableName = pkg.Variables.Add("strTableName", false, "User", SourceServer.ToString() + "." + SourceSchTable.ToString());
Microsoft.SqlServer.Dts.Runtime.Variable pkgServerName = pkg.Variables.Add("strSourceServerName", false, "User", SourceServer.ToString());
//Destination Variables
String DestinationSchema = var["User::strDestinationSchema"].Value.ToString();
String DestinationTable = var["User::strDestinationTable"].Value.ToString();
String SourceArchive = var["User::strSourceArchiveTable"].Value.ToString();
String DestinationSchTable = "[" + DestinationSchema + "].[" + DestinationTable + "]";
SqlCmdForId = SqlCmdForId + DestinationSchTable + " With (nolock)";
//Variable Initialization
FastLoadMaxCommitSize = (Int32)var["User::intFastLoadCommit"].Value;
ArchiveExportId = (Int32)var["User::intArchiveExportId"].Value;
RowsPerBatch = var["User::intFastLoadBatchSize"].Value.ToString();
pkg.Name = PackageName + ArchiveExportId.ToString();
IsFastLoad = (Boolean)var["User::bitIsFastLoad"].Value;
IsOverride = (Boolean)var["User::bitIsSqlOverride"].Value;
IsQuery = (Boolean)var["User::bitIsSqlCmd"].Value;
IsTransferData = (Boolean)var["User::bitIsTransferData"].Value;
IsRetention = (Boolean)var["User::bitIsRetention"].Value;
//Add Connections
ConnectionManager DestinationConMgr = AddOLEDBConnection("destination", Dts.Connections["destination"].ConnectionString.ToString());
ConnectionManager SourceConMgr = AddOLEDBConnection("source", Dts.Connections["source"].ConnectionString.ToString());
ConnectionManager host = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
ConnectionManager msdb = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
//Add Logging
pkgLogging = pkg.LogProviders.Add("DTS.LogProviderSQLServer");
pkgLogging.ConfigString = msdb.Name;
pkgLogging.Description = "Packing logging Using Host msdb";
pkg.LoggingOptions.SelectedLogProviders.Add(pkgLogging);
pkg.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
pkg.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
pkg.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
//Set Isolation Level
pkg.IsolationLevel = IsolationLevel.ReadUncommitted;
//Set Sequences
seg = pkg.Executables.Add("STOCK:Sequence");
seg1 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
seg2 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
seg3 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
seg4 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
seg1.Name = "ArchiveTable_Sequence1";
seg2.Name = "ArchiveTable_Sequence2";
seg3.Name = "ArchiveId_Sequence";
try
{
eDestCnt = seg1.Executables.Add("STOCK:SQLTASK");
DestCnt = (Microsoft.SqlServer.Dts.Runtime.TaskHost)eDestCnt;
DestCnt.FailPackageOnFailure = true;
DestCnt.IsolationLevel = IsolationLevel.ReadCommitted;
DestCnt.Name = "DestinationPrevious";
DestCnt.Properties["Connection"].SetValue(DestCnt, DestinationConMgr.ID);
DestCnt.Properties["SqlStatementSource"].SetValue(DestCnt, SqlCounts + " " + DestinationSchTable);
DestCnt.Properties["BypassPrepare"].SetValue(DestCnt, false);
DestCnt.SetExpression("ResultSetType", "2"); //'2 = single row
DestCnt.ExecValueVariable = DestPrevCnt;
DestCnt1 = (ExecuteSQLTask)DestCnt.InnerObject;
DestRB = DestCnt1.ResultSetBindings.Add();
DestRB.DtsVariableName = "User::intDestPrevCnt";
//Destination CurrentCount
eDestCurr = seg2.Executables.Add("STOCK:SQLTASK");
DestCurrt = (Microsoft.SqlServer.Dts.Runtime.TaskHost)eDestCurr;
DestCurrt.FailPackageOnFailure = true;
DestCurrt.IsolationLevel = IsolationLevel.ReadUncommitted;
DestCurrt.Name = "DestinationCurret";
DestCurrt.Properties["Connection"].SetValue(DestCurrt, DestinationConMgr.ID);
DestCurrt.Properties["SqlStatementSource"].SetValue(DestCurrt, SqlCounts + " " + DestinationSchTable);
DestCurrt.Properties["BypassPrepare"].SetValue(DestCurrt, false);
DestCurrt.SetExpression("ResultSetType", "2");// '2 = single row
DestCnt.ExecValueVariable = DestCurrCnt;
DestCurr1 = (ExecuteSQLTask)DestCurrt.InnerObject;
DestcurrRB = DestCurr1.ResultSetBindings.Add();
DestcurrRB.DtsVariableName = "User::intDestCurrCnt";
//Set Execution Query
if (IsQuery & IsRetention)
{
ExecuteSqlCmd = SourceSqlCmd;
}
else
{
ExecuteSqlCmd = AutoSqlCmd;
}
//DataFlow task
DataFlow = seg3.Executables.Add("STOCK:PipelineTask");
DataFlowTaskHost = ( Microsoft.SqlServer.Dts.Runtime.TaskHost)DataFlow;
DataFlowTaskHost.Name = "ArchiveExportDataFlow";
DataFlowTask = (MainPipe)DataFlowTaskHost.InnerObject;
// Join containers
Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint DestToWf = seg1.PrecedenceConstraints.Add(eDestCnt, DataFlow);
DestToWf.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint WfToDest = seg2.PrecedenceConstraints.Add(DataFlow, eDestCurr);
WfToDest.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
//Add Source Component
SourceComponent = DataFlowTask.ComponentMetaDataCollection.New();
SourceComponent.Name = "SourceTable";
SourceComponent.ComponentClassID = "DTSAdapter.OleDbSource";
// Get Source instance
SourceInstance = SourceComponent.Instantiate();
SourceInstance.ProvideComponentProperties();
//Set Source Connection
SourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = SourceConMgr.ID;
SourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SourceConMgr);
if (IsQuery == true)
{
SourceInstance.SetComponentProperty("AccessMode", 2);
SourceInstance.SetComponentProperty("SqlCommand", ExecuteSqlCmd);
}
else
{
SourceInstance.SetComponentProperty("AccessMode", 0); //0 TableOrView
}
SourceInstance.SetComponentProperty("OpenRowset", SourceSchTable);
//Reinitialize the metadata, Refresh Columns
SourceInstance.AcquireConnections(null);
SourceInstance.ReinitializeMetaData();
SourceInstance.ReleaseConnections();
//Add Multicast
MulticastComponent = DataFlowTask.ComponentMetaDataCollection.New();
MulticastComponent.ComponentClassID = "DTSTransform.Multicast";
//Get Mulicast Instance
MulticastInstance = MulticastComponent.Instantiate();
MulticastInstance.ProvideComponentProperties();
MulticastComponent.Name = "Mulitcast Source Data";
//Add Row Count component
SourceRowCount = DataFlowTask.ComponentMetaDataCollection.New();
SourceRowCount.ComponentClassID = "DTSTransform.RowCount";
rowInstance = SourceRowCount.Instantiate();
rowInstance.ProvideComponentProperties();
rowInstance.SetComponentProperty("VariableName", "User::intSourceCnt");
//Connect multicast and Source
SourceMultiCastPath = DataFlowTask.PathCollection.New();
SourceMultiCastPath.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection[0], MulticastComponent.InputCollection[0]);
//Connect the Multicast And Row count
SourceCntPath = DataFlowTask.PathCollection.New();
SourceCntPath.AttachPathAndPropagateNotifications(MulticastComponent.OutputCollection[0], SourceRowCount.InputCollection[0]);
//Add Table Destination
DestinationComponent = DataFlowTask.ComponentMetaDataCollection.New();
DestinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";
DestinationComponent.ValidateExternalMetadata = true;
//Get Destination Instance and initialize component
DestinationInstance = DestinationComponent.Instantiate();
DestinationInstance.ProvideComponentProperties();
DestinationComponent.Name = "ArchiveDestination";
DestinationComponent.Description =" SQLSat archive Sample";
//Set Source Connection
DestinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = DestinationConMgr.ID;
DestinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface (DestinationConMgr);
//Use straight table transfer
if (IsFastLoad == false)
{
DestinationInstance.SetComponentProperty("AccessMode", 0);
}
else
{
DestinationInstance.SetComponentProperty("AccessMode", 3);
}
DestinationInstance.SetComponentProperty("OpenRowset", DestinationSchTable);
DestinationInstance.SetComponentProperty("FastLoadKeepIdentity", true);
DestinationInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", FastLoadMaxCommitSize);
DestinationInstance.SetComponentProperty("FastLoadKeepNulls", false);
DestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = " + RowsPerBatch);
DestinationInstance.Validate();
DestinationInstance.AcquireConnections(null);
DestinationInstance.ReinitializeMetaData();
DestinationInstance.ReleaseConnections();
//Add Rowcount and Archive Destination
DestinationPath = DataFlowTask.PathCollection.New();
DestinationPath.AttachPathAndPropagateNotifications(SourceRowCount.OutputCollection[0], DestinationComponent.InputCollection[0]);
//mapping
MappColumns(DestinationComponent,DestinationInstance);
SavePackage("C:\\Temp");
if (IsTransferData == true)
{
pkg.Execute();
if(pkg.ExecutionResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure || pkg.ExecutionStatus == Microsoft.SqlServer.Dts.Runtime.DTSExecStatus.Abend)
{
pkg.Name = "Error"+pkg.Name.ToString();
SavePackage("C:\\Temp");
var["User::intIsPkgFail"].Value = (Int32)1;
var["User::intDestCurrCnt"].Value = Failure;
var["User::intDestPrevCnt"].Value = Failure;
var["User::intSourceCnt"].Value = Failure;
Dts.Events.FireError(-1, "Internal Package Error", "Internal Dynamic package Error For ArchiveExportId: " + ArchiveExportId.ToString() + " ErrorMessage:" + GetErrorMessage(pkg), "", 0);
}
}
var.Unlock();
pkg.Dispose();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
pkg.Name = "Error" + pkg.Name.ToString();
SavePackage("C:\\Temp");
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
#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
private void SavePackage(String DirectoryPath)
{
String FullFilePath = DirectoryPath + "\\" + pkg.Name.ToString() + ".dtsx";
if (File.Exists(FullFilePath))
{
File.Delete(FullFilePath);
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.SaveToXml(FullFilePath, pkg, null);
}
else
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
app.SaveToXml(FullFilePath, pkg, null);
}
}
private String GetErrorMessage(Microsoft.SqlServer.Dts.Runtime.Package p )
{
StringBuilder ErrorMessage = new StringBuilder();
foreach(DtsError Message in p.Errors)
{
ErrorMessage.Append(Message.Description.ToString());
}
return ErrorMessage.ToString();
}
/*
* Add OLEDB Connection
*/
public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
{
ConnectionManager ConMgr = pkg.Connections.Add("OLEDB");
ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
ConMgr.Name = ConnectionName;
ConMgr.Description = "SQL OLEDB using " + ConnectionName;
return ConMgr;
}
private void MappColumns(IDTSComponentMetaData100 icmDest, CManagedComponentWrapper mcwDest)
{
//mappings
IDTSInput100 inpDestination = icmDest.InputCollection[0];
IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vinpcDestination = (IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;
foreach (IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
{
try
{
if (vcolumn.DataType != DataType.DT_BYTES)
{
IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_READONLY);
// map external column
IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
}
else
{
IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);
}
}
catch
{
IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);
// map external column
}
}
}
}
}
No comments:
Post a Comment