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.


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.

13 Mar

SSIS Toolbox: Multicast

Multicast, because it has a funky name sounds a bit scary, unlike Fuzzy Grouping, which sounds fuzzily cuddly.  It turns out that not only is it not scary, it’s a super easy topic to write about.  And I need one of those.

Multicast is a transform that takes data and repeats it a number of times in the Data Flow.  In other words, data goes in and it comes out however many times you’d like it to.  Lets do a really quick example using the adventureworks2012 database.

  1. Create a new package and call it whatever you like.
  2. Create a Data Flow task by dragging it to the Control Flow or just clicking the Data Flow tab and hitting the link there.
  3. Drag a OLE DB data source to the data flow.
    1. Point it to the AdventureWorks2012 database, or whatever you have available.  Create a new connection if you need to.
    2. Set the “Data access mode” to “SQL command”.
    3. Use “SELECT TOP(10) firstname, lastname FROM person.person ORDER BY businessentityID, lastname;” for the query.
    4. Click on the Columns tab to make SSIS happy.
  4. Drag a Multicast transform to the desktop and connect the OLE DB source you just created to it.
  5. Drag two UNION ALL transform to the desktop and connect the Multicast to both of them.
  6. Enable the data viewer functionality by clicking on the path to each UNION ALL transform, clicking on the “Data Viewer” tab and checking “Enable data viewer”.

multicastIf you want you can now click on the Multicast transform and it will show you the output properties.  There aren’t many here, and my guess is that you wouldn’t often change these, but it’s good to know that there are some options available.

If you run the package at this point you should see two data viewer windows showing 10 records.