Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Passing Identity Field to DM FLow
Go
New
Search
Notify
Tools
Reply
  
Passing Identity Field to DM FLow
 Login/Join
 
Member
posted
Hi

I have a two part question

As part of a flow, I need to determine the current value of an Identity field in SQL Server, store that value in a variable and pass it to DM Flow.

There is a SQL commeand - SELECT IDENT_CURRENT(TABLE_NAME) - which will return the current identity value.

In the flow, I will increment it and then concatenate that field with a field from a data source.

Is there a library of templates/fex's to look at that demonstrate how to create a fex that will call a sql command,
and store the result in a amper variable?


part 2 -

There will be 2 targets for this flow, but the same table. The soure has a 2 groups of fields - Primary and Secondary.
The target only has a Single set of fields to handle the source fields.

For example

Target A - TABLE_A

Source Target
Prim_city City
Prim_zip Zip


Target B - TABLE_A

Source Target
Sec_city City
Sec_zip Zip


Do i know which of the targets will get processed first ? i.e. Target A, or Target B. I see an issue with calling assigning the correct Identity Value when dealing with multiple targets. Or, should I do this sequentially?

Any help would be appreciated,

Thanks,

Jim
 
Posts: 5 | Registered: August 01, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
You can use a Transact-SQL command in a DBMS SQL Flow. Right click on the SQL object to select Adapter MS SQL Server and the desired connection. Then select SQL Statement and enter

SELECT IDENT_CURRENT('table_name') AS curid

Note that the table name should be enclosed in quotes and that you should give the column a name of your choice, her "curid."

Add a New target and select Adapter of Formatted File and Format Alpha. Enter a synonym name of your choice, say "baseaapp/tabid."

Create a Process Flow that calls your DBMS SQL Flow and your original data flow.

In 7.7 you can simply right click on the Data FLow and check the checkobx "Get Paramaters using Synonym" and for "File Name" enter the name of the table you created, "baseapp/tabid." That will pass the a variable with the same name as the field, "curid."

Now you can run the Process Flow.

Is your target MS SQL Server? If so I don't understand your second question, there should be no need for multiple targets objects if there is just one target table.

Is your target a flat file? If so they get written in the order you added them.
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Hi Clif,

Thank you for your response,

To answer your question, based on the existense of a value in a certain field, i need to create a new record in TABLE_A.

For example, if there is a value in the 'Secondary City' field in the table, then create a new record, otherwise, just write the current record to TABLE_A. This is why i thought about this approach - i.e. 2 targets objects, but actually writing to the same table.
 
Posts: 5 | Registered: August 01, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
Thanks, I understand now. I tried that and it works fine. Use the Validate tab on the second target table to only accept rows with a value in "Second City."
 
Posts: 373 | Location: New York City | Registered: May 03, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    Passing Identity Field to DM FLow

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.