Data Flow Transform Not Automatically Mapping Columns

Question

I'm in the midst of creating a set of SSIS 2014 packages (using Visual Studio 2013) importing data from 50 tables. Since most of the tables are being filtered on the same column and I'm importing all columns into my staging area, I opted for a reusable design where my package name matches the table name, my OLE DB source query is defined with an expression-based variable (starting with SELECT * FROM <tablename>...) and my OLE DB destination table is also defined with an expression-based variable (in this case schema.tablename).

Both the source query and target table have matching column names and match in most cases on data types and sizes.

As I'm cloning my packages for each table, I copy/paste a previously-created package and visually inspect the mappings to confirm the expressions and the data flow mappings are valid for the new package name. I'm seeing that the expressions are working as planned, but I have to manually do the drag/drop mapping of most columns in the destination editor.

Is this expected behavior of the editor as the design metadata of the original package is invalid under the new package name and is there a way to have Visual Studio delete and remap all columns in the data flow based on column name?

Answer 1

Copying and pasting SSIS packages can lead to a few issues. It sounds like you have simple packages - a dataflow with a source to destination. Generally, those don't have the hangup I've run into but I have seen situations where people were inconsistent in their definition of data types. Email - varchar(40), varchar(80), varchar(120), nvarchar(256) all in the same database, just in different tables. What can happen is that you first build your package using the 80 size. When you copy/paste and fix your source table, the editor may not pick up on the data size change when you goto the 40 table because it fits within the old sizing. But I've also seen the reverse - it might not pick up the increased size. Resolution isn't horrific, just change your query to select 1 AS x, let it set the metadata and then fix your query back to the proper select.

As Dave suggested, this task is ideal for an entry level Biml task. In fact, as along as you don't mind not having your explicit source query, my Biml replicate-o-matic is pretty much exactly what you're describing. Earlier incarnation is on SO and I'm linking to it to hedge against link rot.

If you like your approach, you can keep your approach. But, save yourself the clicking and dragging in your destination and replace it with six clicks: three right, three left. In your mappings tab, right click to bring up this context sensitive menu

Left click "Select All Mappings"

Right click and the left click "Delete Selected Mappings"

Right click and then Left click "Map Items by Matching Names"

Answer 2

Yes, even after you resolve references the destination will not properly auto-map. You have to either create a new destination or select a different table for your destination, accept/close, then open and select your intended target and it should now auto-resolve your column associations. I've run into difficulty with that before too though.

With 50 tables now who knows how many in the future, you should check out BIML which will generate the XML for each package and thus (assuming you set everything up correctly) doesn't even need inspection of created packages.