SSIS external metadata column needs to be removed

Question

I am creating a select statement on the fly because the column names and table name can change, but they all need to go into the same data destination. There are other commonalities that make this viable, if I need to later I will go into them. So, what it comes down to is this: I am creating the select statement with 16 columns, there will always be sixteen columns, no more, no less, the column names can change and the table name can change. When I execute the package the select statement gets built just fine but when the Data Flow tries to execute, I get the following error:

The "external metadata column "ColumnName" (79)" needs to be removed from the external metadata column collection.

The actual SQL Statement being generated is:

select 0 as ColumnName, Column88 as CN1, 0 as CN2, 0 as CN3, 0 as CN4, 0 as CN5, 0 as CN6, 0 as CN7, 0 as CN8, 0 as CN9, 0 as CN10, 0 as CN11, 0 as CN12, 0 as CN13, 0 as CN14, 0 as CN15 from Table3

The column 'Column88' is generated dynamicly and so is the table name. If source columns exist for the other ' as CNx' columns, they will appear the same way (Column88 as CN1, Column89 as CN2, Column90 as CN3, etc.) and the table name will always be in the form: Tablex where x is an integer.

Could anyone please help me out with what is wrong and how to fix it?

Thank you.

Answer 1

You're in kind of deep here. You should just take it as read that you can't change the apparent column names or types. The names and types of the input columns become the names and types of the metadata flowing down from the source. If you change those, then everything that depends on them must fail.

The solution is to arrange for these to be stable, perhaps by using column aliases and casts. For one table:

SELECT COLNV, COLINT FROM TABLE1

for another

SELECT CAST(COLV AS NVARCHAR(50)) AS COLNV, CAST(COLSMALL AS INTEGER) AS COLINT FROM TABLE2

Give that a try and see if it works out for you. You just really can't change the metadata without fixing up the entire remainder of the package.

Answer 2

I had the same issue here when I had to remove a column from my stored procedure (which spits out to a temp table) in SQL and add two columns. To resolve the issue, I had to go through each part of my SSIS package from beginning (source - in my case, pulls from a temporary table), all the way through to your destination (in my case a flat file connection to a flat file csv). I had to re-do all the mappings along the way and I watched for errors that game up in the GUI data flow tasks in SSIS.

This error did come up for me in the form of a red X with a circle around it, I hovered over and it mentioned the metadata thing...I double clicked on it and it warned me that one of my columns didn't exist anymore and wanted to know if I wanted to delete it. I did delete it, but I can tell you that this error has more to do with SSIS telling you that your mappings are off and you need to go through each part of your SSIS package to make sure everything is all mapped out correctly.

Answer 3

I'm having this same problem right now. I'm also building a select statement, but mine will always be from the same temp table AND the columns will vary. Sometimes the select statement will call 3 columns, other times 11, and a few inbetween. I would like to know if there's a way you can break a mapping temporarily. While I'm saying this I have doubts going through my mind that this is possible.

My destination is excel, and if I leave some column names out, i'm going to have empty columns, and it's going to look funny!

I hope when microsoft bring out a newer SQL Server, that we'll be able to create column names dynically without having to map columns