Some sample data (source & desired output) would illuminate this for others who are reading ...

You are probably going to end up using the Script Component within a dataflow to...

Use a SQL Server statement as you suggest (you can get the result of a single-valued T-SQL query into a variable (say MaxID) directly from the Execute SQL task).

Then use...

Please consider posting this in a more appropriate forum ... one related to Crystal, perhaps? 🙂

Sounds like an automated backup/restore process might be more efficient - are you effectively just replacing the dev db?

Are the files all of the same format? Are they going to the same destination folder?

Another alternative, of course, is to do it all in one hit at the end, using a simple UPDATE query.

You almost answered your own question. Create a lookup table in SQL Server and then use the Lookup transformation in your dataflow.

Is this an SSIS question?

Oops, sorry. Glad you got it in the end.

Is DateStamp a column name in emDetail?

If so, your syntax is going to be more like this:

update dbo.emDetail

set dateYear = DATEPART('year',datestamp)

Remember single quotes for literals in T-SQL.

To update existing data, you need to use an OLEDB Command transformation. The various different Destination components are for INSERTs only.

Other than "can someone help me", there is no question in your post, it's just a statement about what you're doing.

I think I may have misunderstood your requirement. Are you trying to UPDATE records which are already on the database, rather than doing an INSERT?

You say 'thanks in advance', but all you've done is told us what you are trying to do, without asking for anything. Which part, specifically, would you like help with?

If you double click on your destination in your dataflow and select Mappings, you should see the new derived column as an available input column - just change the mapping...