Creating Base Column Mapping by Script (ColumnMaps)

[Former Member]

Hello PowerDesigner Community,

i am currently developing a PowerShell script (later into PD Script) that should be able to duplicate a mapping for a table, but the new mapping is based on a new datasource.

This is what I have so far:

## ToDo before running the script
### !All relevant Models must be opened
### !New datasource must be already created in the target model
### !Change and validate the initial variable names
## !Initiate Variable Names
$targetModelName = 'Test_Stage'
$duplicatedTableName = 'StageTable47'
$oldSourceModelName = 'Test_Source'
$oldDatasourceName = 'DataSource_Source2Stage'
$newSourceModelName = 'Duplicated_Test_Source'
$newDatasourceName = 'DataSource_Duplicated_Src2Stage'

## Get PowerDesigner window as new object
$powerDesigner = New-Object -com powerdesigner.application

## Select the target model as an object, also select the duplicated table where the mapping should be changed
$targetModel = $powerDesigner.Models | Where Code -eq $targetModelName
$duplicatedTable = $targetModel.Tables | Where Code -eq $duplicatedTableName

## Select the new and old souce models to set or get attributes for the mappings
$newSourceModel = $powerDesigner.Models | Where Code -eq $newSourceModelName
$oldSourceModel = $powerDesigner.Models | Where Code -eq $oldSourceModelName

## Select the new and old datasources
$datasourceFrom = $targetModel.DataSources | Where Code -eq 
$oldDatasourceName$datasourceTo = $targetModel.DataSources | Where Code -eq $newDatasourceName

# Select the old mapping (which got duplicated too)
$oldMapping = $datasourceFrom.GetMapping($duplicatedTable)

# Create a new mapping on the duplicated table
$newMapping = $datasourceTo.CreateMapping($duplicatedTable)

# Set some attributes to the new mapping
$newMapping.SetAttribute('Name', -join($oldMapping.Name,'_duplicated'))
$newMapping.SetAttribute('Code', -join($oldMapping.Code,'_duplicated'))

# Add the new source table (from the new datasource) to the new mapping
$newSourceTable = $newSourceModel.Tables | Where Code -eq $oldMapping.MappedTo
$newMapping.AddSource($newSourceTable)

My question is: How can I create a new Base Column Mapping now so it is exactly like the old mapping but with the new datasource as the parent.

I tried out to copy the old base column mapping but this didnt changed the parent

I also tried out to use CreateNew() Method onto the ColumnMaps Collection of $newMapping but this resulted in an empty Column in the BaseColumnMapping and this is a read only property which I cant change after the creation.

I need the method (constructor) to create a base column mapping with a specified column.

Thanks,

Stephan

Update:

Some Additional Information:

This is what the current Mapping Editor looks like after using the script:

[Former Member]

Hi Stephan, George,

Did you try ExcelImport for this and is this likely to work?

I'm not a programmer and don't have easy access to one yet (unfortunately).

I was trying to do the same via ExcelImport. I was planning to:
- first, import Data Sources
- then, mappings (Entity to Table)
- then, SubObjectMappings (Attribute to Column)

I already got stuck at data sources though.

The data in my excel (1 line):

When I import I get: 1 warning has been logged into the output window.

Opening document C:\Users\<user>\Desktop\export DS.xls Importing data from table
DS 16:59:53
Error: Could not find object with Name = LDM_2 1 object(s) updated
Closing document export DS.xls 16:59:54

LDM_2 was open in my workspace and is a Logical Data Model.

The Data source object is created, with Name, Model Type and Access Type set properly, but not Model (as log indicates).

Problem seems to be related to the model tab. If I try to add a model manually, it is pointing to "physical model" type (instead of model type Logical Data Model). This seems to be standard behavior (I can reproduce).

Hence my thought: maybe I'm going about this the wrong way.

Is excel import a dead end for what I'm trying to do?

Regards, Raf

[GeorgeMcGeachie]

I suspect you'll need to tell the Excel import to serach for referenced objects in other models - see the import options