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