Replace column value with MAX() query in dataflow before insert

Hi,

We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.

Just to be clear:
column id (int)
column name (string)

Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table

Which transformation do I use to replace the value of the id column with this new id?

thanks,

Stephane

Hi Stephane,

You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:

ISNULL(id) ? maxid + 1 : id

Mark

|||

Hi Mark,

Thanks for your reply.

Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.

Thanks,

Stephane

sql