public void GenerateColumnForVirtualInput(ConnectionManager conMgrDestination, IDTSComponentMetaData100 flatFileDestination, string ColumnDelimeter, bool UniCode) { // Add columns to the FlatFileConnectionManager wrap.IDTSConnectionManagerFlatFile100 fileConnection = null; fileConnection = conMgrDestination.InnerObject as wrap.IDTSConnectionManagerFlatFile100; fileConnection.Unicode = UniCode; DtsConvert.GetExtendedInterface(conMgrDestination); if (fileConnection == null) { throw new Exception("Invalid file connection"); } // Get the upstream columns IDTSVirtualInputColumnCollection100 vColumns = flatFileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection; for (int cols = 0; cols < vColumns.Count; cols++) { wrap.IDTSConnectionManagerFlatFileColumn100 col = fileConnection.Columns.Add(); // If this is the last column, set the delimiter to CRLF. // Otherwise keep the delimiter as ",". if (cols == vColumns.Count - 1) { col.ColumnDelimiter = "\r\n"; } else { if (ColumnDelimeter == "\\t" || ColumnDelimeter == "Tab {t}") { col.ColumnDelimiter = "\t"; } else { col.ColumnDelimiter = ColumnDelimeter; } col.ColumnDelimiter = "\t"; } col.ColumnType = "Delimited"; col.DataType = vColumns[cols].DataType; col.DataPrecision = vColumns[cols].Precision; col.DataScale = vColumns[cols].Scale; wrap.IDTSName100 name = col as wrap.IDTSName100; name.Name = vColumns[cols].Name; } }
public override ConnectionManager AddConnectionManager(Package package) { if (colNames.Length != colTypes.Length || colTypes.Length != colLengths.Length || colLengths.Length != colPrecisions.Length || colPrecisions.Length != colScales.Length || colNames.Length <= 0) { throw new InvalidOperationException("The number of elements in the colNames, colTypes, colLengths, colPrecisions and colScales are not equal or empty"); } ConnectionManager connMgr = package.Connections.Add("FLATFILE"); connMgr.Name = ConnectionMgrName; connMgr.ConnectionString = filePath; /// Set the custom properties for flat file connection mgr wrapper.IDTSConnectionManagerFlatFile100 ffConMgr = connMgr.InnerObject as wrapper.IDTSConnectionManagerFlatFile100; ffConMgr.Format = "Delimited"; // can be parameterized, use "Delimited" for simplicity ffConMgr.ColumnNamesInFirstDataRow = columnNamesinFirstRow; ffConMgr.RowDelimiter = "\r\n"; // can be parameterized, use "\r\n" for simplicity wrapper.IDTSConnectionManagerFlatFileColumns100 ffCols = ffConMgr.Columns; int numCols = colTypes.Length; for (int i = 0; i < numCols; i++) { wrapper.IDTSConnectionManagerFlatFileColumn100 ffCol = ffCols.Add(); ffCol.ColumnType = "Delimited"; ffCol.ColumnDelimiter = columnDelimiter; ffCol.DataType = colTypes[i]; ffCol.MaximumWidth = colLengths[i]; // this sets the OutputColumnWidth ffCol.DataPrecision = colPrecisions[i]; ffCol.DataScale = colScales[i]; wrapper.IDTSName100 colName = ffCol as wrapper.IDTSName100; colName.Name = colNames[i]; } ffCols[numCols - 1].ColumnDelimiter = ffConMgr.RowDelimiter; // last col use row delimiter return(connMgr); }
public FlatFileConnectionManager AddAdvancedColumn(FlatFileColumn flatFileColumn) { Wrapper.IDTSConnectionManagerFlatFileColumn100 newColumn = FlatFileConnection.Columns.Add(); newColumn.MaximumWidth = flatFileColumn.OutputColumnWidth; newColumn.ColumnDelimiter = Convert(ColumnDelimiter); newColumn.TextQualified = flatFileColumn.TextQualified; newColumn.ColumnType = flatFileColumn.ColumnType; if (flatFileColumn.InputColumnWidth > 0) { newColumn.ColumnWidth = flatFileColumn.InputColumnWidth; } else { newColumn.ColumnWidth = flatFileColumn.OutputColumnWidth; } newColumn.DataType = (Wrapper.DataType)flatFileColumn.DataType; newColumn.DataScale = flatFileColumn.DataScale; newColumn.DataPrecision = flatFileColumn.DataPrecision; Wrapper.IDTSName100 columnName = newColumn as Wrapper.IDTSName100; columnName.Name = flatFileColumn.Name; return(this); }
public override void Exec() { try { UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer; UIHierarchyItem hierItem = ((UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0)); ProjectItem pi = (ProjectItem)hierItem.Object; Window win = pi.Document.ActiveWindow; IDesignerHost designer = (IDesignerHost)pi.Document.ActiveWindow.Object; Package package = null; ConnectionManager conn = GetSelectedConnectionManager(designer, out package); BIDSHelper.SSIS.FixedWidthColumnsForm form = new BIDSHelper.SSIS.FixedWidthColumnsForm(); if (conn != null && conn.Properties["Format"].GetValue(conn).ToString() == "FixedWidth") { //hiding properties for ragged right form.dataGridView1.Height = form.dataGridView1.Height + 50 + 26; form.dataGridView1.Top -= 50; form.label1.Height -= 50; form.cboRaggedRightDelimiter.Visible = false; form.lblRaggedRight.Visible = false; } DialogResult dialogResult = form.ShowDialog(); if (dialogResult == DialogResult.OK) { #if KATMAI || DENALI || SQL2014 wrap.IDTSConnectionManagerFlatFile100 ff = conn.InnerObject as wrap.IDTSConnectionManagerFlatFile100; DtsConvert.GetExtendedInterface(conn); #else wrap.IDTSConnectionManagerFlatFile90 ff = conn.InnerObject as wrap.IDTSConnectionManagerFlatFile90; DtsConvert.ToConnectionManager90(conn); #endif while (ff.Columns.Count > 0) { ff.Columns.Remove(0); } List <string> listUsedNames = new List <string>(); //JCW - Added counter to identify the last column int columnCount = 1; foreach (DataGridViewRow row in form.dataGridView1.Rows) { string sName = row.Cells[0].Value.ToString().Trim(); string sOriginalName = sName; int i = 1; while (listUsedNames.Contains(sName)) //find a unique name for the column { sName = sOriginalName + (++i); } listUsedNames.Add(sName); #if KATMAI || DENALI || SQL2014 wrap.IDTSConnectionManagerFlatFileColumn100 col = ff.Columns.Add(); wrap.IDTSName100 name = col as wrap.IDTSName100; #else wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add(); wrap.IDTSName90 name = col as wrap.IDTSName90; #endif name.Name = sName; col.MaximumWidth = int.Parse(row.Cells[1].Value.ToString()); col.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR; if (columnCount == form.dataGridView1.Rows.Count && form.cboRaggedRightDelimiter.Text != "[None]") { col.ColumnWidth = 0; col.ColumnType = "Delimited"; col.ColumnDelimiter = DecodeDelimiter(form.cboRaggedRightDelimiter.Text); } else { col.ColumnWidth = int.Parse(row.Cells[1].Value.ToString()); col.ColumnType = "FixedWidth"; } columnCount++; } //mark package object as dirty IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService)); changesvc.OnComponentChanging(package, null); changesvc.OnComponentChanged(package, null, null, null); //marks the package designer as dirty SSISHelpers.MarkPackageDirty(package); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void SetDtsColumnProperties(wrap.IDTSConnectionManagerFlatFileColumn100 flatFileColumn, SqlDataReader reader, string ColumnDelimeter, bool AllowFlatFileTruncate) { flatFileColumn.ColumnType = "Delimited"; if (ColumnDelimeter == "\\t" || ColumnDelimeter == "Tab {t}") { flatFileColumn.ColumnDelimiter = "\t"; } else { flatFileColumn.ColumnDelimiter = ColumnDelimeter; } switch (Convert.ToInt16(reader["xtype"])) { case 104: // DT_BOOL bit flatFileColumn.DataType = wrap.DataType.DT_BOOL; break; case 173: // DT_BYTES binary, varbinary, timestamp case 165: case 189: flatFileColumn.DataType = wrap.DataType.DT_BYTES; flatFileColumn.ColumnWidth = Convert.ToInt32(reader["length"]); if (!AllowFlatFileTruncate) { flatFileColumn.MaximumWidth = Convert.ToInt32(reader["length"]); } break; case 60: // DT_CY smallmoney, money case 122: flatFileColumn.DataType = wrap.DataType.DT_CY; flatFileColumn.DataPrecision = Convert.ToInt32(reader["prec"]); flatFileColumn.DataScale = (int)reader["scale"]; break; case 61: // DT_DBTIMESTAMP datetime, smalldatetime case 58: flatFileColumn.DataType = wrap.DataType.DT_DBTIMESTAMP; break; case 36: // DT_GUID uniqueidentifier flatFileColumn.DataType = wrap.DataType.DT_GUID; break; case 52: // DT_I2 smallint flatFileColumn.DataType = wrap.DataType.DT_I2; break; case 56: // DT_I4 int flatFileColumn.DataType = wrap.DataType.DT_I4; break; case 127: // DT_I8 bigint flatFileColumn.DataType = wrap.DataType.DT_I8; break; case 106: // DT_NUMERIC decimal, numeric case 108: flatFileColumn.DataType = wrap.DataType.DT_NUMERIC; flatFileColumn.DataPrecision = Convert.ToInt32(reader["prec"]); flatFileColumn.DataScale = (int)reader["scale"]; break; case 59: // DT_R4 real flatFileColumn.DataType = wrap.DataType.DT_R4; break; case 62: // DT_R8 float flatFileColumn.DataType = wrap.DataType.DT_R8; break; case 175: // DT_STR char, varchar case 167: flatFileColumn.DataType = wrap.DataType.DT_STR; flatFileColumn.ColumnWidth = Convert.ToInt32(reader["length"]); if (!AllowFlatFileTruncate) { flatFileColumn.MaximumWidth = Convert.ToInt32(reader["length"]); } break; case 48: // DT_UI1 tinyint flatFileColumn.DataType = wrap.DataType.DT_UI1; break; case 239: // DT_WSTR nchar, nvarchar, sql_variant, xml case 231: case 98: case 241: flatFileColumn.DataType = wrap.DataType.DT_WSTR; flatFileColumn.ColumnWidth = Convert.ToInt32(reader["length"]) / 2; if (!AllowFlatFileTruncate) { flatFileColumn.MaximumWidth = Convert.ToInt32(reader["length"]) / 2; } break; case 34: // DT_IMAGE image flatFileColumn.DataType = wrap.DataType.DT_IMAGE; break; case 99: // DT_NTEXT ntext flatFileColumn.DataType = wrap.DataType.DT_NTEXT; break; case 35: // DT_TEXT text flatFileColumn.DataType = wrap.DataType.DT_TEXT; break; } wrap.IDTSName100 columnName = flatFileColumn as wrap.IDTSName100; columnName.Name = reader["name"].ToString(); }