private void GetColumns <T>(ref IDTSConnectionManagerFlatFile100 manager, EntityTypeConfiguration <T> map) where T : class { foreach (object column in manager.Columns) { manager.Columns.Remove(column); } Type type = typeof(T); T instance = (T)Activator.CreateInstance(type); IEnumerable <Tuple <string, string, int?, int?> > columnInfo = map.GetColumnInfo() .OrderBy(o => o.Item3); Dictionary <string, int> numberLengths = new Dictionary <string, int> { { "LOGRECNO", 7 }, { "AREALAND", 14 }, { "AREAWATR", 14 }, { "POP100", 9 }, { "HU100", 9 } }; foreach (Tuple <string, string, int?, int?> column in columnInfo) { //Console.WriteLine(column); int?columnWidth = numberLengths.ContainsKey(column.Item1) ? numberLengths[column.Item1] : column.Item4; if (!columnWidth.HasValue) { throw new ArgumentException($"Invalid Column width on {column.Item1}"); } dynamic ffColumnObject = manager.Columns.Add(); IDTSConnectionManagerFlatFileColumn100 ffColumn = (IDTSConnectionManagerFlatFileColumn100)ffColumnObject; ffColumn.DataType = column.Item2.Equals("NVARCHAR") ? DataType.DT_STR : DataType.DT_I4; ffColumn.ColumnType = "FixedWidth"; ffColumn.ColumnWidth = columnWidth.Value; ffColumn.MaximumWidth = ffColumn.ColumnWidth; IDTSName100 columnName = ffColumnObject as IDTSName100; if (columnName != null) { columnName.Name = column.Item1; columnName.Description = instance.GetName(column.Item1); } } dynamic ffTerminatingColumnObject = manager.Columns.Add(); IDTSConnectionManagerFlatFileColumn100 ffTerminatingColumn = (IDTSConnectionManagerFlatFileColumn100)ffTerminatingColumnObject; ffTerminatingColumn.DataType = DataType.DT_STR; ffTerminatingColumn.ColumnWidth = 1; ffTerminatingColumn.MaximumWidth = 1; IDTSName100 terminatingColumnName = ffTerminatingColumnObject as IDTSName100; if (terminatingColumnName != null) { terminatingColumnName.Name = "TERMINATOR"; terminatingColumnName.Description = "A single space terminating column to ensure all record rows are matching lengths"; } }
private static void AddColumn(IDTSDesigntimeComponent100 designtimeComponent, IDTSConnectionManagerFlatFileColumn100 column, int outputID, int columnIndex, int codePage) { IDTSName100 nameID = column as IDTSName100; IDTSOutputColumn100 outputColumn = designtimeComponent.InsertOutputColumnAt(outputID, columnIndex, nameID.Name, nameID.Description); int codePageValue = (column.DataType == DataType.DT_STR || column.DataType == DataType.DT_TEXT) ? codePage : 0; designtimeComponent.SetOutputColumnDataTypeProperties(outputID, outputColumn.ID, column.DataType, column.MaximumWidth, column.DataPrecision, column.DataScale, codePageValue); }
public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask, out Microsoft.SqlServer.Dts.Runtime.Application app) { package = new Microsoft.SqlServer.Dts.Runtime.Package(); Executable exec = package.Executables.Add("STOCK:PipelineTask"); Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost; dataFlowTask = thMainPipe.InnerObject as MainPipe; ComponentEventHandler events = new ComponentEventHandler(); dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents); // Create a flat file source ConnectionManager flatFileConnectionManager = package.Connections.Add("FLATFILE"); flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited"); flatFileConnectionManager.Properties["Name"].SetValue(flatFileConnectionManager, "Flat File Connection"); flatFileConnectionManager.Properties["ConnectionString"].SetValue(flatFileConnectionManager, @".\TextDataToBeHashed.txt"); flatFileConnectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileConnectionManager, false); flatFileConnectionManager.Properties["HeaderRowDelimiter"].SetValue(flatFileConnectionManager, "\r\n"); flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\""); flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0); flatFileConnectionManager.Properties["Unicode"].SetValue(flatFileConnectionManager, false); flatFileConnectionManager.Properties["CodePage"].SetValue(flatFileConnectionManager, 1252); // Create the columns in the flat file IDTSConnectionManagerFlatFile100 flatFileConnection = flatFileConnectionManager.InnerObject as IDTSConnectionManagerFlatFile100; IDTSConnectionManagerFlatFileColumn100 StringDataColumn = flatFileConnection.Columns.Add(); StringDataColumn.ColumnDelimiter = ","; StringDataColumn.ColumnType = "Delimited"; StringDataColumn.DataType = DataType.DT_STR; StringDataColumn.DataPrecision = 0; StringDataColumn.DataScale = 0; StringDataColumn.MaximumWidth = 255; ((IDTSName100)StringDataColumn).Name = "StringData"; IDTSConnectionManagerFlatFileColumn100 MoreStringColumn = flatFileConnection.Columns.Add(); MoreStringColumn.ColumnDelimiter = ","; MoreStringColumn.ColumnType = "Delimited"; MoreStringColumn.DataType = DataType.DT_STR; MoreStringColumn.DataPrecision = 0; MoreStringColumn.DataScale = 0; MoreStringColumn.MaximumWidth = 255; ((IDTSName100)MoreStringColumn).Name = "MoreString"; IDTSConnectionManagerFlatFileColumn100 DateColumn = flatFileConnection.Columns.Add(); DateColumn.ColumnDelimiter = ","; DateColumn.ColumnType = "Delimited"; DateColumn.DataType = DataType.DT_DATE; DateColumn.DataPrecision = 0; DateColumn.DataScale = 0; DateColumn.MaximumWidth = 0; ((IDTSName100)DateColumn).Name = "DateColumn"; IDTSConnectionManagerFlatFileColumn100 IntegerColumn = flatFileConnection.Columns.Add(); IntegerColumn.ColumnDelimiter = ","; IntegerColumn.ColumnType = "Delimited"; IntegerColumn.DataType = DataType.DT_I4; IntegerColumn.DataPrecision = 0; IntegerColumn.DataScale = 0; IntegerColumn.MaximumWidth = 0; ((IDTSName100)IntegerColumn).Name = "IntegerColumn"; IDTSConnectionManagerFlatFileColumn100 NumericColumn = flatFileConnection.Columns.Add(); NumericColumn.ColumnDelimiter = "\r\n"; NumericColumn.ColumnType = "Delimited"; NumericColumn.DataType = DataType.DT_NUMERIC; NumericColumn.DataPrecision = 15; NumericColumn.DataScale = 2; NumericColumn.MaximumWidth = 0; ((IDTSName100)NumericColumn).Name = "NumericColumn"; app = new Microsoft.SqlServer.Dts.Runtime.Application(); IDTSComponentMetaData100 flatFileSource = dataFlowTask.ComponentMetaDataCollection.New(); flatFileSource.ComponentClassID = app.PipelineComponentInfos["Flat File Source"].CreationName; // Get the design time instance of the Flat File Source Component var flatFileSourceInstance = flatFileSource.Instantiate(); flatFileSourceInstance.ProvideComponentProperties(); flatFileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flatFileConnectionManager); flatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID; // Reinitialize the metadata. flatFileSourceInstance.AcquireConnections(null); flatFileSourceInstance.ReinitializeMetaData(); flatFileSourceInstance.ReleaseConnections(); flatFileSource.CustomPropertyCollection["RetainNulls"].Value = true; //[MD5BinaryOutput] varbinary(16), [MD5HexOutput] varchar(34), [MD5BaseOutput] varchar(24))"; multipleHash = dataFlowTask.ComponentMetaDataCollection.New(); multipleHash.ComponentClassID = typeof(Martin.SQLServer.Dts.MultipleHash).AssemblyQualifiedName; multipleHashInstance = multipleHash.Instantiate(); multipleHashInstance.ProvideComponentProperties(); multipleHash.Name = "Multiple Hash Test"; multipleHashInstance.ReinitializeMetaData(); // Create the path from source to destination. StaticTestUtilities.CreatePath(dataFlowTask, flatFileSource.OutputCollection[0], multipleHash, multipleHashInstance); // Select the input columns. IDTSInput100 multipleHashInput = multipleHash.InputCollection[0]; IDTSVirtualInput100 multipleHashvInput = multipleHashInput.GetVirtualInput(); foreach (IDTSVirtualInputColumn100 vColumn in multipleHashvInput.VirtualInputColumnCollection) { multipleHashInstance.SetUsageType(multipleHashInput.ID, multipleHashvInput, vColumn.LineageID, DTSUsageType.UT_READONLY); } // Add the output columns // Generate the Lineage String lineageString = String.Empty; foreach (IDTSInputColumn100 inputColumn in multipleHashInput.InputColumnCollection) { if (lineageString == String.Empty) { lineageString = String.Format("#{0}", inputColumn.LineageID); } else { lineageString = String.Format("{0},#{1}", lineageString, inputColumn.LineageID); } } }