public static void WritePackage(bool validateExternalMetadata, string baseDirectoryPath, Project project, Configuration configuration, DataTransfer @object
/*, ConnectionManager sourceConnectionManager, ConnectionManager destinationConnectionManager*/)
{
if ((object)project == null)
throw new ArgumentNullException(nameof(project));
if ((object)configuration == null)
throw new ArgumentNullException(nameof(configuration));
if ((object)@object == null)
throw new ArgumentNullException(nameof(@object));
/*if ((object)sourceConnectionManager == null)
throw new ArgumentNullException("sourceConnectionManager");
if ((object)destinationConnectionManager == null)
throw new ArgumentNullException("destinationConnectionManager");*/
Console.WriteLine("*** {0} ***", @object.Source.ToString());
using (Package package = new Package())
{
project.PackageItems.Add(package, GetSsisObjectPackageName(@object.Source));
ConnectionManager sourceConnectionManager;
ConnectionManager dictionaryConnectionManager;
ConnectionManager destinationConnectionManager;
TaskHost sqlTaskHost;
ExecuteSQLTask executeSqlTask;
TaskHost dataFlowTaskHost;
MainPipe mainPipe;
IDTSComponentEvents dtsComponentEvents;
PrecedenceConstraint sqlTask_dataFlowTask_PrecedenceConstraint;
IDTSComponentMetaData100 oleDbSourceDtsComponentMetaData100;
IDTSDesigntimeComponent100 oleDbSourceDtsDesigntimeComponent100;
IDTSComponentMetaData100 oleDbDictionaryDtsComponentMetaData100;
IDTSDesigntimeComponent100 oleDbDictionaryDtsDesigntimeComponent100;
IDTSComponentMetaData100 transformDtsComponentMetaData100;
IDTSDesigntimeComponent100 transformDtsDesigntimeComponent100;
IDTSComponentMetaData100 oleDbDestinationDtsComponentMetaData100;
IDTSDesigntimeComponent100 oleDbDestinationDtsDesigntimeComponent100;
IDTSPath100 source_transform_DtsPath100;
IDTSPath100 transform_destination_DtsPath100;
IDTSPath100 source_destination_DtsPath100;
IDTSInput100 dtsInput100;
IDTSVirtualInput100 dtsVirtualInput100;
IDTSInputColumnCollection100 dtsInputColumnCollection100;
IDTSExternalMetadataColumnCollection100 dtsExternalMetadataColumnCollection100;
IDTSOutputColumnCollection100 dtsOutputColumnCollection100;
IDTSInputColumn100 dtsInputColumn100;
IDTSExternalMetadataColumn100 dtsExternalMetadataColumn100;
bool injectTransform = true;
// create source connection
sourceConnectionManager = package.Connections.Add("OLEDB");
sourceConnectionManager.Name = "Source OLEDB Connection Manager";
sourceConnectionManager.ConnectionString = GetSourceConnectionString(@object.Source);
if (injectTransform)
{
// create dictionary connection
dictionaryConnectionManager = package.Connections.Add("OLEDB");
dictionaryConnectionManager.Name = "Dictionary OLEDB Connection Manager";
dictionaryConnectionManager.ConnectionString = GetDictionaryConnectionString(configuration.Dictionary);
}
else
dictionaryConnectionManager = null;
// create destination connection
destinationConnectionManager = package.Connections.Add("OLEDB");
destinationConnectionManager.Name = "Destination OLEDB Connection Manager";
destinationConnectionManager.ConnectionString = GetDestinationConnectionString(@object.Destination);
// create SQL task
sqlTaskHost = (TaskHost)package.Executables.Add("STOCK:SQLTask");
sqlTaskHost.Name = "Execute SQL Task";
// get inner object
executeSqlTask = (ExecuteSQLTask)sqlTaskHost.InnerObject;
executeSqlTask.Connection = destinationConnectionManager.ID;
executeSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
executeSqlTask.SqlStatementSource = GetTruncateTableCommandText(configuration.TruncateDestination, @object.Destination);
// create data flow task
dataFlowTaskHost = (TaskHost)package.Executables.Add("STOCK:PipelineTask");
dataFlowTaskHost.Name = "Data Flow Task";
// get inner object
mainPipe = (MainPipe)dataFlowTaskHost.InnerObject;
// capture COM events
dtsComponentEvents = new ConsoleComponentEventHandler();
mainPipe.Events = DtsConvert.GetExtendedInterface(dtsComponentEvents);
// wire together
sqlTask_dataFlowTask_PrecedenceConstraint = package.PrecedenceConstraints.Add(sqlTaskHost, dataFlowTaskHost);
sqlTask_dataFlowTask_PrecedenceConstraint.Value = DTSExecResult.Success;
// -----------------------------------------------------------
// meanwhile, inside the data flow task...
// -----------------------------------------------------------
// create OLEDB source component metadata in pipeline
oleDbSourceDtsComponentMetaData100 = mainPipe.ComponentMetaDataCollection.New();
oleDbSourceDtsComponentMetaData100.ComponentClassID = "DTSAdapter.OleDbSource";
oleDbSourceDtsComponentMetaData100.ValidateExternalMetadata = validateExternalMetadata;
oleDbSourceDtsComponentMetaData100.Name = "OLE DB Source";
// create OLEDB source design-time component in pipeline
oleDbSourceDtsDesigntimeComponent100 = oleDbSourceDtsComponentMetaData100.Instantiate();
oleDbSourceDtsDesigntimeComponent100.ProvideComponentProperties();
oleDbSourceDtsDesigntimeComponent100.SetComponentProperty("AccessMode", 0);
oleDbSourceDtsDesigntimeComponent100.SetComponentProperty("OpenRowset", @object.Source.ToString(false));
// set OLEDB source connection manager
oleDbSourceDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_SOURCE].ConnectionManager = DtsConvert.GetExtendedInterface(sourceConnectionManager);
oleDbSourceDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_SOURCE].ConnectionManagerID = sourceConnectionManager.ID;
// get the column metadata
if (validateExternalMetadata)
{
oleDbSourceDtsDesigntimeComponent100.AcquireConnections(null);
oleDbSourceDtsDesigntimeComponent100.ReinitializeMetaData();
oleDbSourceDtsDesigntimeComponent100.ReleaseConnections();
}
if (injectTransform)
{
// create transform component metadata in pipeline
transformDtsComponentMetaData100 = mainPipe.ComponentMetaDataCollection.New();
transformDtsComponentMetaData100.ComponentClassID = typeof(ObfuscationStrategyTransform).AssemblyQualifiedName;
transformDtsComponentMetaData100.ValidateExternalMetadata = validateExternalMetadata;
transformDtsComponentMetaData100.Name = "Obfuscation Strategy Transform";
// create transform design-time component in pipeline
transformDtsDesigntimeComponent100 = transformDtsComponentMetaData100.Instantiate();
transformDtsDesigntimeComponent100.ProvideComponentProperties();
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_DEBUGGER_LAUNCH, false);
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_DICTIONARY_CONFIGURATION, "[]");
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_SIGN_HASH_MULTIPLIER, 33);
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_SIGN_HASH_SEED, 5381);
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_VALUE_HASH_MULTIPLIER, 33);
transformDtsDesigntimeComponent100.SetComponentProperty(Constants.COMPONENT_PROP_NAME_VALUE_HASH_SEED, 5381);
// set OLEDB dictionary connection manager on transform
transformDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_SOURCE].ConnectionManager = DtsConvert.GetExtendedInterface(dictionaryConnectionManager);
transformDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_SOURCE].ConnectionManagerID = dictionaryConnectionManager.ID;
// get the column metadata
if (validateExternalMetadata)
{
transformDtsDesigntimeComponent100.AcquireConnections(null);
transformDtsDesigntimeComponent100.ReinitializeMetaData();
transformDtsDesigntimeComponent100.ReleaseConnections();
}
}
else
{
// do nothing
transformDtsComponentMetaData100 = null;
}
// create OLEDB destination component metadata in pipeline
oleDbDestinationDtsComponentMetaData100 = mainPipe.ComponentMetaDataCollection.New();
oleDbDestinationDtsComponentMetaData100.ComponentClassID = "DTSAdapter.OleDbDestination";
oleDbDestinationDtsComponentMetaData100.ValidateExternalMetadata = validateExternalMetadata;
oleDbDestinationDtsComponentMetaData100.Name = "OLE DB Destination";
// create OLEDB destination design-time component in pipeline
oleDbDestinationDtsDesigntimeComponent100 = oleDbDestinationDtsComponentMetaData100.Instantiate();
oleDbDestinationDtsDesigntimeComponent100.ProvideComponentProperties();
oleDbDestinationDtsDesigntimeComponent100.SetComponentProperty("AccessMode", 3);
oleDbDestinationDtsDesigntimeComponent100.SetComponentProperty("FastLoadKeepIdentity", true);
oleDbDestinationDtsDesigntimeComponent100.SetComponentProperty("FastLoadKeepNulls", true);
oleDbDestinationDtsDesigntimeComponent100.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,FIRE_TRIGGERS");
oleDbDestinationDtsDesigntimeComponent100.SetComponentProperty("OpenRowset", @object.Destination.ToString(false));
// set OLEDB destination connection manager
oleDbDestinationDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_DESTINATION].ConnectionManager = DtsConvert.GetExtendedInterface(destinationConnectionManager);
oleDbDestinationDtsComponentMetaData100.RuntimeConnectionCollection[Constants.COMPONENT_RUNTIMECONNECTION_IDX_DESTINATION].ConnectionManagerID = destinationConnectionManager.ID;
// get the column metadata
if (validateExternalMetadata)
{
oleDbDestinationDtsDesigntimeComponent100.AcquireConnections(null);
oleDbDestinationDtsDesigntimeComponent100.ReinitializeMetaData();
oleDbDestinationDtsDesigntimeComponent100.ReleaseConnections();
}
if (injectTransform)
{
// connect the dots: OLEDB source -> obfuscation strategy transform
source_transform_DtsPath100 = mainPipe.PathCollection.New();
source_transform_DtsPath100.AttachPathAndPropagateNotifications(oleDbSourceDtsComponentMetaData100.OutputCollection[0], transformDtsComponentMetaData100.InputCollection[Constants.COMPONENT_INPUT_DEFAULT_NAME]);
// connect the dots: obfuscation strategy transform -> OLEDB destination
transform_destination_DtsPath100 = mainPipe.PathCollection.New();
transform_destination_DtsPath100.AttachPathAndPropagateNotifications(transformDtsComponentMetaData100.OutputCollection[Constants.COMPONENT_OUTPUT_DEFAULT_NAME], oleDbDestinationDtsComponentMetaData100.InputCollection[0]);
}
else
{
// connect the dots: OLEDB source -> OLEDB destination
source_destination_DtsPath100 = mainPipe.PathCollection.New();
source_destination_DtsPath100.AttachPathAndPropagateNotifications(oleDbSourceDtsComponentMetaData100.OutputCollection[0], oleDbDestinationDtsComponentMetaData100.InputCollection[0]);
}
// for the OLEDB destination: requires hooking up the external columns
dtsInput100 = oleDbDestinationDtsComponentMetaData100.InputCollection[0];
dtsVirtualInput100 = dtsInput100.GetVirtualInput();
dtsInputColumnCollection100 = dtsInput100.InputColumnCollection;
dtsExternalMetadataColumnCollection100 = dtsInput100.ExternalMetadataColumnCollection;
dtsOutputColumnCollection100 = oleDbSourceDtsComponentMetaData100.OutputCollection[0].OutputColumnCollection;
foreach (IDTSOutputColumn100 dtsOutputColumn100 in dtsOutputColumnCollection100)
{
// [optional place to skip columns here]
if (@object.ExcludeMemberNames.Any(c => c.SafeToString().Trim().ToLower() ==
dtsOutputColumn100.Name.SafeToString().Trim().ToLower()))
continue;
// get the external column ID
dtsExternalMetadataColumn100 = dtsExternalMetadataColumnCollection100.Cast<IDTSExternalMetadataColumn100>().SingleOrDefault(emc => emc.Name.SafeToString().Trim().ToLower() == dtsOutputColumn100.Name.SafeToString().Trim().ToLower());
//dtsExternalMetadataColumn100 = dtsExternalMetadataColumnCollection100[dtsOutputColumn100.Name];
if ((object)dtsExternalMetadataColumn100 != null)
{
// create an input column from an output column of previous component.
dtsVirtualInput100.SetUsageType(dtsOutputColumn100.ID, DTSUsageType.UT_READONLY);
dtsInputColumn100 = dtsInputColumnCollection100.GetInputColumnByLineageID(dtsOutputColumn100.ID);
if ((object)dtsInputColumn100 != null)
{
// map the input column with an external metadata column
oleDbDestinationDtsDesigntimeComponent100.MapInputColumn(dtsInput100.ID, dtsInputColumn100.ID, dtsExternalMetadataColumn100.ID);
}
}
else
Console.WriteLine(dtsOutputColumn100.Name);
}
WritePackageApplication(baseDirectoryPath, @object.Source, package);
}
}
private static void WriteProject(string baseDirectoryPath, Configuration configuration)
{
if ((object)configuration == null)
throw new ArgumentNullException(nameof(configuration));
using (Project project = Project.CreateProject())
{
/*ConnectionManagerItem sourceConnectionManagerItem;
ConnectionManagerItem destinationConnectionManagerItem;
ConnectionManager sourceConnectionManager;
ConnectionManager destinationConnectionManager;*/
project.Name = string.Format(SsisProjectName);
/*sourceConnectionManagerItem = project.ConnectionManagerItems.Add("OLEDB", "Source.conmgr");
sourceConnectionManagerItem.ConnectionManager.Name = "Source OLEDB Connection Manager";
sourceConnectionManagerItem.ConnectionManager.ConnectionString = GetSourceConnectionString(FourPartName);
sourceConnectionManager = sourceConnectionManagerItem.ConnectionManager;
destinationConnectionManagerItem = project.ConnectionManagerItems.Add("OLEDB", "Destination.conmgr");
destinationConnectionManagerItem.ConnectionManager.Name = "Destination OLEDB Connection Manager";
destinationConnectionManagerItem.ConnectionManager.ConnectionString = GetDestinationConnectionString(FourPartName);
destinationConnectionManager = destinationConnectionManagerItem.ConnectionManager;*/
foreach (var @object in configuration.Objects)
WritePackage(configuration.ValidateExternalMetadata ?? true, baseDirectoryPath, project, configuration, @object);
// MASTER: server ...
// SERVER: database ...
// DATABASE: schema ...
// SCHEMA: objtype...
// OBJTYPE: ...
// OBJECT .
var serverNameCts = configuration.Objects
.GroupBy(o => o.Source.ServerName)
.Select(cl => new
{
_4PN = cl.First().Source,
ServerName = cl.First().Source.ServerName,
Count = cl.Count()
});
WriteParentPackage(baseDirectoryPath, project, SsisRootPackageName, serverNameCts.Select(cl => GetSsisServerPackageName(cl._4PN)).ToArray());
foreach (var serverNameCt in serverNameCts)
{
Console.WriteLine("{0}{1}({2})", new string(' ', 0), serverNameCt.ServerName, serverNameCt.Count);
var databaseNameCts = configuration.Objects.Where(o => o.Source.ServerName == serverNameCt.ServerName)
.GroupBy(o => o.Source.DatabaseName)
.Select(cl => new
{
_4PN = cl.First().Source,
DatabaseName = cl.First().Source.DatabaseName,
Count = cl.Count()
});
WriteParentPackage(baseDirectoryPath, project, GetSsisServerPackageName(serverNameCt._4PN), databaseNameCts.Select(cl => GetSsisDatabasePackageName(cl._4PN)).ToArray());
foreach (var databaseNameCt in databaseNameCts)
{
Console.WriteLine("{0}{1}({2})", new string(' ', 1), databaseNameCt.DatabaseName, databaseNameCt.Count);
var schemaNameCts = configuration.Objects.Where(o => o.Source.ServerName == serverNameCt.ServerName && o.Source.DatabaseName == databaseNameCt.DatabaseName)
.GroupBy(o => o.Source.SchemaName)
.Select(cl => new
{
_4PN = cl.First().Source,
SchemaName = cl.First().Source.SchemaName,
Count = cl.Count()
});
WriteParentPackage(baseDirectoryPath, project, GetSsisDatabasePackageName(databaseNameCt._4PN), schemaNameCts.Select(cl => GetSsisSchemaPackageName(cl._4PN)).ToArray());
foreach (var schemaNameCt in schemaNameCts)
{
Console.WriteLine("{0}{1}({2})", new string(' ', 2), schemaNameCt.SchemaName, schemaNameCt.Count);
var objectTypeCts = configuration.Objects.Where(o => o.Source.ServerName == serverNameCt.ServerName && o.Source.DatabaseName == databaseNameCt.DatabaseName && o.Source.SchemaName == schemaNameCt.SchemaName)
.GroupBy(o => o.Source.ObjectType)
.Select(cl => new
{
_4PN = cl.First().Source,
ObjectType = cl.First().Source.ObjectType,
Count = cl.Count()
});
WriteParentPackage(baseDirectoryPath, project, GetSsisSchemaPackageName(schemaNameCt._4PN), objectTypeCts.Select(cl => GetSsisObjectTypePackageName(cl._4PN)).ToArray());
foreach (var objectTypeCt in objectTypeCts)
{
Console.WriteLine("{0}{1}({2})", new string(' ', 3), objectTypeCt.ObjectType, objectTypeCt.Count);
var objectCts = configuration.Objects.Where(o => o.Source.ServerName == serverNameCt.ServerName && o.Source.DatabaseName == databaseNameCt.DatabaseName && o.Source.SchemaName == schemaNameCt.SchemaName && o.Source.ObjectType == objectTypeCt.ObjectType)
.GroupBy(o => o.Source.ObjectName)
.Select(cl => new
{
_4PN = cl.First().Source,
ObjectName = cl.First().Source.ObjectName,
Count = cl.Count()
});
WriteParentPackage(baseDirectoryPath, project, GetSsisObjectTypePackageName(objectTypeCt._4PN), objectCts.Select(cl => GetSsisObjectPackageName(cl._4PN)).ToArray());
// complete
}
}
}
}
project.SaveTo(Path.Combine(baseDirectoryPath, SsisArchiveFileName));
}
}
public static void ToJsonFile(Configuration configuration, string jsonFile)
{
new JsonSerializationStrategy().SetObjectToFile<Configuration>(jsonFile, configuration);
}
