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