15 Mar

SSIS Toolbox: Character Map

The Character Map transform makes changes to string functions.  For instance, it can upper case a string (exactly like the UPPER function or it can do a couple of other things that most of us will probably never use.  As such, this will be a quick one.

Step 1:  Create a OLE DB source

  1. Click on the Data Flow tab, hit the link and create a data flow.
  2. Drag a OLE DB source to the desktop.
  3. Configure it as follows:
    1. OLE DB connection manager: Adventureworks 2012.  Click new and create if necessary.
    2. Data access mode: SQL command.
    3. SQL command text: “SELECT name FROM production.location”.
    4. Click the columns tab..
    5. Click OK to close.

Step 2: Multicast the results

Drag a Multicast transform to the Data Flow and connect the OLE DB source to it.  You don’t need to configure anything else.

charactermap

Character Map.

  1. Drag two Character Map transforms to the Data Flow.
  2. Connect both Character Map transforms to the Multicast transform.
  3. Configure the first transform.
    1. Name it Uppercase (F2 on the transform or click on the name).
    2. Double-click the transform to open it.
    3. Check the name box (it will be the only one).
    4. Set the Operation field to Uppercase.
    5. Click OK to close it.
  4. Configure the second transform exactly the same except for the following.Configure the first transform.
    1. Name it Chinese.
    2. Set the Operation field to “Simplified Chinese” and “Byte Reversal”.

Step 4: Create two UNION ALL and configure them both to use the data viewer.

  1. Drag two UNION ALL transforms to the Data Flow.
  2. Connect them to the two Character Map transforms.
  3. On the paths from the Character Map transforms to the UNION ALL transforms, double-click, click the “Data Viewer Tab” and check the “Enable data viewer” box.
  4. Click OK to close them.
The finished product.

The finished product.

Run the package.  If you don’t get anything in Chinese, I would say don’t worry about it, and move on, I did.