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);
}
}
}
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);
}