Newly added column missing from Available Destination Columns in ADO NET Destination

Question

I need to add a column to an ETL process. Completed all steps successfully except the last one - ADO NET Destination (the "Insert Specs" element on the left).

The new column appears just fine in the Conditional Split, both as a source column and destination column.

In the ADO NET Destination ("Insert Specs") the new column only appears on the left in the "Available Input Columns" and it's missing on the right ("Available Destination Columns").

I know for sure my new column is present in the destination table. Did I miss a step along the way?

---

Update:
What's also interesting is that when I hover over the ADO NET Destination element I get this warning:

A new column with name sold_price has been added to the external database table. Use advanced editor to refresh available destination columns if needed.

Solution 1

The view above is for "Edit".

The instructions however ask for the "Advanced Editor".

There is a "Refresh" button at the bottom of the Advanced Editor. Clicking this button adds the new column to the "Available Destination Columns" box.

Solution 2

I have found this happens in other transformations from time to time. The only way I have to consistently fix it, is to delete the data flow connectors and reconnect them, as sometimes they don't seem to get refreshed in SSIS 2008.

Solution 3

This is for SSIS 2013: In destination editor, there is button "New..." next to "Name of the table or view". Press it, edit table name in code, press OK. This will drop your current destination table and create new one, with updated columns, which you can map now. Be careful, as this will drop your current destination table.

Solution 4

The accepted answer works but causes all of the Input Column mappings to reset (at least for me, I'm using Visual Studio 2017). I was able to add the new column by going into the Input and Output Properties tab, expanding ADO NET Destination Input, selecting External Columns and clicking the Add Column button. The new field now becomes available in the Destination Column in the Column Mappings tab.

Solution 5

I had few "Union All" components used in my ssis package and that is where new columns were lost. I had to update it and include new columns in unions and it worked after that.

View more solutions

Comments

[Nelu]

Just tried it and the column is still missing from Available Destination Columns. :(

[KidCode]

Any idea how to update the available input columns? I.E when you add a new column to the source that supplies the conditional split?

[Kris]

I tried this and my new column still isn't showing up. Any ideas of what else I could try? I'd rather not have to delete and re-add everything but maybe that's the only option left?

[Alic]

The button is missing in 2013 version