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 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);
}
}
public static void ConfigureConnectionManager(ConnectionManager cm, SSISFlatFileConnectionProperties prop, Dictionary <string, MyColumn> columnCollection, ILogger logger)
{
cm.Description = "connect to a flat file";
logger.Debug("DE added FlatFile connection to {File}", prop.ConnectionString);
//set connection properties
mwrt.IDTSConnectionManagerFlatFile100 fcm = cm.InnerObject as mwrt.IDTSConnectionManagerFlatFile100;
cm.Properties["ConnectionString"].SetValue(cm, prop.ConnectionString);
fcm.CodePage = prop.CodePage;
fcm.Unicode = prop.Unicode;
fcm.ColumnNamesInFirstDataRow = prop.ColumnNamesInFirstDataRow;
fcm.DataRowsToSkip = prop.DataRowsToSkip;
fcm.Format = prop.Format;
fcm.HeaderRowDelimiter = prop.HeaderRowDelimiter;
fcm.HeaderRowsToSkip = prop.HeaderRowsToSkip;
fcm.RowDelimiter = prop.RecordDelimiter;
if (prop.TextQualifier != null)
{
fcm.TextQualifier = prop.TextQualifier;
}
mwrt.IDTSConnectionManagerFlatFileColumn100 fColumn;
mwrt.IDTSName100 fName;
if (columnCollection != null && columnCollection.Count > 0)
{
//define input column
int i = 1;
foreach (MyColumn dsvCol in columnCollection.Values)
{
fColumn = fcm.Columns.Add();
fColumn.ColumnType = fcm.Format;
fColumn.DataType = dsvCol.DataType;
fColumn.DataPrecision = dsvCol.Precision;
fColumn.TextQualified = (fcm.TextQualifier != null &&
(dsvCol.DataType == mwrt.DataType.DT_STR ||
dsvCol.DataType == mwrt.DataType.DT_WSTR));
fColumn.ColumnDelimiter = (columnCollection.Count == i) ? prop.RecordDelimiter : prop.ColumnDelimiter;
//fColumn.ColumnDelimiter = (dsv.ColumnCollection.Count == i) ? "\r\n" : "\t";
fColumn.DataScale = dsvCol.Scale;
fColumn.MaximumWidth = dsvCol.Length;
fName = (mwrt.IDTSName100)fColumn;
fName.Name = dsvCol.Name;
i++;
}
}
else if (prop.ColumnNamesInFirstDataRow)
{
//use file header
string header = string.Empty;
using (StreamReader sr = File.OpenText(cm.ConnectionString))
{
for (int l = 0; l <= fcm.HeaderRowsToSkip; l++)
{
header = sr.ReadLine();
}
sr.Close();
}
string[] del = new string[] { prop.ColumnDelimiter };
string[] cols = header.Split(del, StringSplitOptions.None);
int i = 1;
foreach (string col in cols)
{
fColumn = fcm.Columns.Add();
fColumn.ColumnType = fcm.Format;
fColumn.DataType = (fcm.Unicode) ? mwrt.DataType.DT_WSTR : mwrt.DataType.DT_STR;
fColumn.TextQualified = (fcm.TextQualifier != null);
fColumn.ColumnDelimiter = (cols.Length == i) ? prop.RecordDelimiter : prop.ColumnDelimiter;
//fColumn.ColumnDelimiter = (dsv.ColumnCollection.Count == i) ? "\r\n" : "\t";
fColumn.MaximumWidth = 255;
fName = (mwrt.IDTSName100)fColumn;
fName.Name = col;
i++;
}
}
}
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;
}