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