1

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();
Nils
  • 9,682
  • 6
  • 46
  • 72
gematzab
  • 181
  • 1
  • 3
  • 14
  • Yeah, I'm pretty sure you can't do this. You might want to look into BiML instead. – Tab Alleman Oct 19 '16 at 12:43
  • Ok. Is it because I am running the script inside the package?? What if I move my script to a parent package and try change the connection managers of the child packages?? – gematzab Oct 19 '16 at 13:38
  • No, I think you just can't make a change to a connection manager's columns that will persist beyond the script. As soon as the script is done running, the connection manager's columns will revert to the way they were created at design time. – Tab Alleman Oct 19 '16 at 13:46

0 Answers0