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

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.

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.

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."