Exemple #1
0
		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);
			}
		}
Exemple #2
0
		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));
			}
		}
Exemple #3
0
		public static void ToJsonFile(Configuration configuration, string jsonFile)
		{
			new JsonSerializationStrategy().SetObjectToFile<Configuration>(jsonFile, configuration);
		}