public ConnectionManager GetFlatFileConnection <T>(string path, EntityTypeConfiguration <T> map, bool updateExisting = false) where T : class
{
if (string.IsNullOrWhiteSpace(path) ||
!File.Exists(path))
{
throw new ArgumentException($"'{path}' is not a valid flat file path.");
}
FileInfo fileInfo = new FileInfo(path);
string fileName = fileInfo.Name.Replace(fileInfo.Extension, "")
.Trim('.');
string name = $"SSIS Flat File Connection Manager for {fileName}";
string description = $"Provides an SSIS connection to {path}";
string streamName = $"{fileInfo.Name}.{ConnectionManagerExtension}";
ConnectionManager manager = CensusPackageConnections.Contains(name) ? CensusPackageConnections[name] : CensusPackageConnections.Add("FLATFILE");
manager.ConnectionString = path;
manager.Name = name;
manager.Description = description;
IDTSConnectionManagerFlatFile100 ffManager = manager.InnerObject as IDTSConnectionManagerFlatFile100;
if (ffManager != null)
{
ffManager.AlwaysCheckForRowDelimiters = true;
ffManager.ColumnNamesInFirstDataRow = false;
ffManager.Unicode = false;
ffManager.CodePage = CodePage;
ffManager.LocaleID = LocaleID;
ffManager.RowDelimiter = RowDelimiter;
ffManager.Format = "FixedWidth";
GetColumns(ref ffManager, map);
}
return(manager);
}
private static void ResetFlatFileColumnProperties(IDTSConnectionManagerFlatFile100 cmff, Dictionary<string, Dictionary<string, string>> hash)
{
int count = 1;
foreach (string item in hash.Keys)
{
IDTSConnectionManagerFlatFileColumn100 cmffc = cmff.Columns.Add();
IDTSName100 col_name = cmffc as IDTSName100;
col_name.Name = item;
int precision = 0;
int scale = 0;
if (hash[item]["precision"] != "")
{
try
{
precision = int.Parse(hash[item]["precision"]);
}
catch (Exception)
{
Console.WriteLine("The precision you have for column {0} cannot convert to integer", item);
Console.WriteLine("I have this: {0} for the precision", hash[item]["precision"]);
System.Environment.Exit(0);
}
}
if (hash[item]["scale"] != "")
{
try
{
scale = int.Parse(hash[item]["scale"]);
}
catch (Exception)
{
Console.WriteLine("The scale you have for column {0} cannot convert to integer", item);
Console.WriteLine("I have this: {0} for the scale", hash[item]["scale"]);
System.Environment.Exit(0);
}
}
// in ssis the last columns column delimiter should be the row delimiter of the file....don't ask...
if (count == hash.Keys.Count)
{
cmffc.ColumnType = "Delimited";
cmffc.ColumnDelimiter = hash[item]["rowdelim"];
try
{
cmffc.DataType = (DataType)Enum.Parse(typeof(DataType), hash[item]["SSIS"]);
}
catch (Exception)
{
Console.WriteLine("This SSIS datatype is incorrect '{0}' at column '{1}'", hash[item]["SSIS"], item);
System.Environment.Exit(0);
}
cmffc.MaximumWidth = precision;
if(hash[item]["textqualified"] != "NULL")
cmffc.TextQualified = true;
else
cmffc.TextQualified = false;
}
else
{
if (precision > 0)
{
cmffc.MaximumWidth = precision;
cmffc.DataPrecision = precision;
}
if (scale > 0)
{
cmffc.DataScale = scale;
}
cmffc.ColumnType = "Delimited";
try
{
cmffc.DataType = (DataType)Enum.Parse(typeof(DataType), hash[item]["SSIS"]);
}
catch (Exception)
{
Console.WriteLine("This SSIS datatype is incorrect '{0}' at column '{1}'", hash[item]["SSIS"], item);
System.Environment.Exit(0);
}
cmffc.ColumnDelimiter = hash[item]["coldelim"];
if (hash[item]["textqualified"] != "NULL")
cmffc.TextQualified = true;
else
cmffc.TextQualified = false;
}
count++;
}
}
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";
}
}
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);
}
}
}