gematzab
gematzab

Reputation: 181

SSIS programatically update column Names and data type

I have created a script task that reads an excel file and tries to update the connection manager columns' details.

BUT even if it runs without errors, the changes are not "saved", so the package maintains the previous details of the columns.

After the program reads and stores the information (ColumnNames[], MaxWidth[] ..etc.).

  1. Deletes the existing columns of the connection manager:

    //INITIALIZE CONNECTION MANAGER OBJECTS
        ConnectionManager conn = Dts.Connections["Flat File Connection Manager"];
        var connectionFlatF = (Wrapper.IDTSConnectionManagerFlatFile100)conn.InnerObject;
    
    
        foreach (Wrapper.IDTSConnectionManagerFlatFileColumn100 column in connectionFlatF.Columns)
        {
            connectionFlatF.Columns.Remove(column);
        }
    
  2. Creates the columns and assigns the realted information:

     for (i = 0; i < ColumnNames.Count; i++) {
    
            //Add column
            Wrapper.IDTSConnectionManagerFlatFileColumn100 flatfilecolumn = (Wrapper.IDTSConnectionManagerFlatFileColumn100)connectionFlatF.Columns.Add();
            //Add column details
            flatfilecolumn.ColumnType = "Delimited";
            flatfilecolumn.ColumnDelimiter = "\t";
            flatfilecolumn.ColumnWidth = 0;
            if (FinalType[i] == "[DT_STR]")
            {
                flatfilecolumn.MaximumWidth = Int32.Parse(MaxWidth[i]);
            }
            else if (FinalType[i] == "[DT_NUMERIC]")
            {
                flatfilecolumn.DataPrecision = Int32.Parse(Percision[i]);
                flatfilecolumn.DataScale = Int32.Parse(Scale[i]);
            }
    
            //Column name
            Wrapper.IDTSName100 columnname = (Wrapper.IDTSName100)flatfilecolumn;
            columnname.Name = ColumnNames[i];
    
    
        }
        conn.Dispose();
    

Upvotes: 1

Views: 741

Answers (0)

Related Questions