source and target table mapping

dataservices job design
1

Hi,

I have very simple DataFlow,

SQL -> query transformation-> Target table

The column name and order in Query transformation and target table are different, I need to do column mapping. Where can do this? I tried drag and drop it didn’t work.

When I use temp table template it is working, but I need to load data to existing table and table is imported to the DataStore. Not sure what I am missing.

thanks in advance.

ThanksForHelp (BOB member since 2006-09-25)

2

Hello,

I’m not sure about the difficulties you have, since this seem a fairly simple task.

In short, in order to accompish the mapping you just have to change the name of the target columns in the query transform to match the names of the fields of the target table. By default, the target loading is done matching tha names of the fields, although I’d recommended that you respect the order of the fields (We’ve had some problems when doing Bulk loading). Also, is they are missing columns in the last query before the target, DS just warn of that and fill the target column with NULL.

I`d recommend you reread the tutorials or the SAP blogs and begginner’s Guides if you still has problems with this.

Regards,

Andrés

aidelia :argentina: (BOB member since 2006-02-02)

3

Start with just a source and target table in your Dataflow. Then add a Query transform but do not open it. Connect the target table to the Query transform first, then connect the source table. Now open the Query transform. The columns from the target table should be in the output schema of the Query transform. You’ll need to do the mapping now.

eganjp :us: (BOB member since 2007-09-12)

4

Are you using the Designer on your own laptop/workstation or are you using the Designer on a virtual server/client to which you use Citrix or RDP to connect to?

The Designer Client is a very old-school C++ application that uses a lot of hook calls into the Windows layer to fetch cursor positions, etc. This is how contextual copy and paste is achieved in the Designer Client for instance - there is a lot of polling between the Designer application and the Windows UI.

Why do I mention that? Because the Designer Client was created before the use of virtualized environments, Citrix and RDP became mainstream and it’s not uncommon for the Designer Client to be problematic in Citrix/RDP environments especially when dealing with high latency issues.

Where I am getting at? It’s not unusual in my experience to run into “drag and drop” issues in the Designer, especially in Citrix/RDP environments. Like dragging columns across but nothing get dragged - that could mean you’re trying to do this too quickly. Try again and be slower.

Or dragging the columns across and they refuse to get added to the target schema. Or you drag a column across and the Designer refuses to come up with the “Insert Above, Insert Below” menu. Closing the Query Task and opening it again may help. Sometimes closing the Designer and opening again may help. That usually does it but sometimes I’ve had to create the columns and mappings manually as the Designer wouldn’t budge at all - this was on an older version of DS 4.0 over a crappy WAN connection.

ErikR :new_zealand: (BOB member since 2007-01-10)