Question - new to BO Data Services Designer - how to automatically transform varchar column data to upper(varchar) across all columns in a table?

[Former Member]

Hello -

New user to BO Data Services Designer. Company is using Data Services Version 12.2.

I have many tables that all need the same transformation- converting varchars fields to upper(varchar) fields

Example:

I have a table called Items. It has 40 columns, 25 of which are of type varchar.

What I have been doing is ....

I make the Item table as the source table then create a Query transform that is then attached to a new target table called - ITEMS_Production.

I can manually drag and drop columns from the source table to the query and then in the mapping area I can manually type in upper(table_name.column_name) or select the upper function and then select the table.column name from the function drop down list.

Obviously, I want to do this quicker as I have to do this for lots and lots of tables.

How can set up Data Services so that I can drag and drop an upper transform quickly and easily or automate this process.

I also know Python-Java so am happy to script something if need be.

the logic would be something like -

if there is a column with a type varchar, add the upper() transformation to column. Go to next column.

Excited to be using this new tool-

Thanks in advance.

Ray

[former_member187605 · Accepted solution]

Use the DS Workbench.

[Former Member · Accepted solution]

Hi Dirk,

Thanks for your response- the company I just joined doesn't have the DS Workbench installed.

Is there an alternative route to do this type of scripting - i.e. drag and drop a custom function?

It seems I can't install DS Workbench on the older version of Data Integrator Professional- Designer 12.2.2.2,  the company is doing the upgrade in August.

Thanks again,

Regards,

Ray

Re: Question - new to BO Data Services Designer - how to automatically transform varchar column data...

[former_member187605 · Accepted solution]

Sorry, I didn't pay proper attention to the version you've mentioned in your OP.
In DS Designer, there's no alternative to the manual work. I usually drag-and-drop all columns from source to target. Then change one mapping to include the function. Then, copy-and-paste the function name and opening bracket to any other mappings and eventually add the closing brackets to make them mappings syntactically correct. It's cumbersome, I know. That's why we have those new features in the DS Workbench, available only from DS 4.0 on.