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);
}
/// <summary>
/// ctor that accepts the parent flat file connection manager and a name for the flat file column
/// </summary>
/// <param name="fileConnection"></param>
/// <param name="name"></param>
public ISFlatFileColumn(ISBaseFlatFileConnectionManager fileConnection, string name)
{
bool exists = false;
_parentConnectionManager = fileConnection;
foreach (runTimeWrapper.IDTSConnectionManagerFlatFileColumn100 column in fileConnection.Columns_m)
{
if (((runTimeWrapper.IDTSName100)column).Name == name)
{
exists = true;
FlatFileColumn = column;
}
}
if (!(exists))
{
FlatFileColumn = fileConnection.Columns_m.Add();
Name = name;
}
}
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();
}
public void AddVirtualColumnsFromTarget(ConnectionManager conMgrSource, IDTSComponentMetaData100 sourceTask,
string TargetName, string ColumnDelimeter, bool IsColumnNamesInFirstDataRow, bool AllowFlatFileTruncate, bool UniCode, string strConn)
{
// Get native flat file connection
string serverName = string.Empty;
string databaseName = string.Empty;
string password = string.Empty;
string userID = string.Empty;
string sqlConnection = string.Empty;
foreach (string connString in strConn.Split(';'))
{
if (connString.Contains("Data Source"))
{
serverName = connString.Substring(connString.IndexOf("=") + 1);
}
else if (connString.Contains("Initial Catalog"))
{
databaseName = connString.Substring(connString.IndexOf("=") + 1);
}
else if (connString.Contains("User ID"))
{
userID = connString.Substring(connString.IndexOf("=") + 1);
}
else if (connString.Contains("Password"))
{
password = connString.Substring(connString.IndexOf("=") + 1);
}
}
if (userID != null && userID != string.Empty)
{
sqlConnection = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};", serverName, databaseName, userID, password);
}
else
{
sqlConnection = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", serverName, databaseName);
}
conMgrSource.Properties["Format"].SetValue(conMgrSource, "Delimited");
conMgrSource.Properties["ColumnNamesInFirstDataRow"].SetValue(conMgrSource, IsColumnNamesInFirstDataRow);
wrap.IDTSConnectionManagerFlatFile100 connectionFlatFile = conMgrSource.InnerObject as wrap.IDTSConnectionManagerFlatFile100;
connectionFlatFile.Unicode = UniCode;
// Connect to SQL server and examine metadata of target table, but must exclude
// extra Flat File FileNameColumnName (FileName) column as that is added by source
SqlConnection connection = new SqlConnection(sqlConnection);
SqlCommand command = new SqlCommand("SELECT name, xtype, length, scale, prec FROM sys.syscolumns " + "WHERE id = OBJECT_ID(@OBJECT_NAME) AND name <> 'FileName'", connection);
command.Parameters.Add(new SqlParameter("@OBJECT_NAME", TargetName));
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
// Create Flat File columns based on SQL columns
while (reader.Read())
{
// Create Flat File column to match SQL target column
wrap.IDTSConnectionManagerFlatFileColumn100 flatFileColumn =
connectionFlatFile.Columns.Add() as wrap.IDTSConnectionManagerFlatFileColumn100;
SetDtsColumnProperties(flatFileColumn, reader, ColumnDelimeter, AllowFlatFileTruncate);
}
}
// Check we have columns
if (connectionFlatFile.Columns.Count == 0)
{
throw new ArgumentException(string.Format("No flat file columns have been created, " +
"check that the destination table '{0}' exists.", TargetName));
}
//Correct the last Flat File column delimiter, needs to be NewLine not Comma
connectionFlatFile.Columns[connectionFlatFile.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;
}
/// <summary>
/// Helper Method
/// </summary>
/// <param name="fc"></param>
/// <param name="name"></param>
private void SetFlatFileColumnName(runTimeWrapper.IDTSConnectionManagerFlatFileColumn100 fc, string name)
{
((runTimeWrapper.IDTSName100)fc).Name = name;
}
/// <summary>
/// Internal ctor that accepts a IDTSConnectionManagerFlatFileColumn100
/// </summary>
/// <param name="inputColumn"></param>
internal ISFlatFileColumn(runTimeWrapper.IDTSConnectionManagerFlatFileColumn100 inputColumn)
{
FlatFileColumn = inputColumn;
}
//automatically define flat file columns from connection string and
public void DefineColumnsInCM(FlatFileFormat format = FlatFileFormat.Delimited, bool reinitmetadata = false)
{
string strline = string.Empty;
string[] strVal = null;
string[] strSamples = null;
int intCount = 0;
List <int> ColumnsLength = new List <int>();
if (ConnectionString == null)
{
return;
}
using (System.IO.StreamReader csStreamReader = new System.IO.StreamReader(ConnectionString))
{
while (csStreamReader.EndOfStream == false)
{
strline = csStreamReader.ReadLine();
if (strVal == null)
{
strVal = strline.Split(this.ColumnDelimiter.ToCharArray());
int intColumnLength = 0;
while (intColumnLength <= strVal.Length - 1)
{
intColumnLength = intColumnLength + 1;
ColumnsLength.Add(50);
}
}
else if (intCount <= 100)
{
strSamples = null;
strSamples = strline.Split(this.ColumnDelimiter.ToCharArray());
intCount = intCount + 1;
int intColumnLength = 0;
while (intColumnLength <= ColumnsLength.Count - 1)
{
if (strSamples[intColumnLength].Length > ColumnsLength[intColumnLength])
{
ColumnsLength[intColumnLength] = strSamples[intColumnLength].Length;
}
intColumnLength = intColumnLength + 1;
}
}
else
{
break;
}
}
}
int intLength = 0;
foreach (string strColumn in strVal)
{
RunWrap.IDTSConnectionManagerFlatFileColumn100 fc = null;
if (fc == null)
{
fc = this.Columns.Add();
}
fc.DataType = RunWrap.DataType.DT_STR;
fc.DataPrecision = 0;
fc.DataScale = 0;
fc.ColumnWidth = ColumnsLength[intLength];
fc.MaximumWidth = ColumnsLength[intLength];
(fc as RunWrap.IDTSName100).Name = strColumn;
intLength = intLength + 1;
}
this.ColumnNamesInFirstDataRow = true;
this.RowDelimiter = "\r\n";
switch (format)
{
case FlatFileFormat.Delimited:
this.ColumnType = FlatFileColumnType.Delimited;
this.ColumnDelimiter = ",";
break;
case FlatFileFormat.FixedWidth:
this.ColumnType = FlatFileColumnType.FixedWidth;
this.ColumnDelimiter = null;
break;
case FlatFileFormat.Mixed: // "FixedWidth with row delimiters"
this.ColumnType = FlatFileColumnType.FixedWidth;
this.ColumnDelimiter = null;
RunWrap.IDTSConnectionManagerFlatFileColumn100 fc = this.Columns.Add();
fc.DataType = RunWrap.DataType.DT_WSTR;
fc.ColumnType = FlatFileColumnType.Delimited.ToString();
fc.ColumnDelimiter = "\r\n";
fc.ColumnWidth = 0;
(fc as RunWrap.IDTSName100).Name = "Row delimiter column";
break;
case FlatFileFormat.RaggedRight:
this.ColumnType = FlatFileColumnType.FixedWidth;
this.ColumnDelimiter = null;
// update the last column to be delimited
this.Columns[this.Columns.Count - 1].ColumnType = FlatFileColumnType.Delimited.ToString();
this.Columns[this.Columns.Count - 1].ColumnDelimiter = "\r\n";
break;
}
if (reinitmetadata)
{
this.Parent.ReinitializeMetaData();
}
}
