You do not need to extract data into a holding area first - just do it all in one statement, eg (untested):

INSERT into [dbname].[schema].target(Field1, Field2, ... Fieldn)

SELECT Field1, Field2, ......

This would seem to come down to MS's definition of 'standard' and 'advanced' transforms - has anyone seen them listed/categorised one by one?

That was a constructive comment, in my opinion. Perhaps a little terse.

It seems that you already know exactly what you want to do and how it should be implemented. I...

You really are new!

I suggest that you search around on the Internet for something like "Creating your first SSIS package" to find the sort of step-by-step detail that you are...

If both of your tables are in SQL Server, just issue the relevant INSERT and UPDATE queries using Execute SQL tasks ...

Or avoid SSIS completely and use a standard SQL...

It's always easier to find something when you know roughly where it is:-D Glad I could help.

On your dataflow, if your source is DataReader, Excel or OLE DB, you have the option of setting the SQL Command property within that source.

In your case, set it along...

Not done it myself, but in the Execute SQL task change source type to FileConnection and then set the FileConnection property accordingly ... Looks like it should work.

I made this work by creating a script transformation component and adding an extra 'RowNum' column as an output.

Within the script component, after defining the new output column, you just...

See my preceding post - the line in bold. It was not showing correctly before, so I just edited it. Maybe that was your problem.

Add a Script task after your SQL task.

Click on Script and In ReadOnlyVariables, enter User::InDatabase

Click on Design Script and enter just one line in Main() to make it look like...

Edit the task. Choose 'Expressions' and create a new one by clicking on the ellipsis, then select SourceDatabaseName as your property and enter the variable expression under 'Expression' ...

I think I would handle this slightly differently.

I would split the input columns so that the numeric data is in one column and the 'special' character is in another.

I would...

There is an SSIS Data Flow Transformation called a Merge Join, which takes as its inputs (sorted) data from two datasets and merges them in the way you want.

The staging...

Or, depending on the nature of your source, you could have used 'SQL Command' as your data access mode and added a suitable WHERE clause to exclude the unwanted data.