CODEWITHSUNDEEP
sql-servervisual-studio-2012ssis
Juan Velez
Juan Velez

Reputation: 741

SSIS Data Conversion to OLE DB Destination column mapping

I am using Visual Studio 2012 (Shell) to create an SSIS package that will Import a Flat File into a SQL Server DB table.

I have a Flat File Source and an OLE DB Destination with a Data Conversion component in between.

I use the Data Conversion tool to convert all my columns from the Source to Unicode String [DT_WSTR] so I wont get the unicode to non unicode error.

I left the default Output Alias names given to the columns by the Data Conversion component which is just the original Input Column name with the words "Copy of" added in front.

I have over 200 columns. When I double click on my Destination Component and go to Map my columns, I see all the original column names showing first in the drop down of available columns to map and then when I scroll down past the first 200 original column names I finally get to my Converted column names (the ones that say Copy of).

I don't know how I was doing it earlier but all I would have to do was hit the down arrow key twice and it would switch from "Column 0" to "Copy of Column 0" so I was basically able to tab thru and quickly map the columns. (tab, tab, down arrow key, down arrow key and repeat).

Now for some reason I have to press the down arrow key over 200 times to get past all of the original columns names to then be able to select the transformed column names which will be very time consuming (and probably cause RSI).

I want to know if I can do either of the following:

1)make it so that only the transformed column names show up in the available input columns when I am mapping the columns in the OLE DB Destination.

or

2)Figure out how I was able to just hit the down arrow key twice when selecting the Input column and then it would switch from the Original Column name to the transformed column name (without having to hit the down arrow key over 200 times to get to the transformed column names).

enter image description here

Upvotes: 1

Views: 6094

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

I would always make sure that metadata columns you want to output to the table have the same name as the destination columns, manually matching columns with different name columns is just too error prone. Same names means you can just right-click the mapping and select map by name. There are several ways to get around this:

First option change the column names in your Flat File Connection Manager by appending an X to the name - they don't have to be the same names that are present in the actual file because it all works by column position. Then in your data conversion you can remove the 'Copy of' and the X from the name and call it exactly what you want.

Second option - insert a Derived Column task between your Data Conversion and your OLE DB Destination. For each non 'Copy of'column select replace and add the matching 'Copy of' column to the expression.

Third and my favorite - insert a Union All task between your Data Conversion and your OLE DB Destination. in the Union All mapping you can rename any column by editing the output column name field and you can right-click over any column to delete it. So you remove all the non 'Copy of' columns and rename the 'Copy of's giving exactly what you need. It's a tad strange using a Union All with one input but it works.

Upvotes: 2

Related Questions