DSXchange

How about giving us a hint, just a little hint, on what isn't working?

Well, how can you put the value of "null" (as a string) into an integer field? What happens is that the value of "null" is not valid, you have not set a default value and the field is nullable - so it uses a null value which I gather is what you want. So you don't need the transformer logic.

perhaps extra spaces on the lookup string? Is the key dataype identical (same length if of type char())?

With the upsert method you will either get the data row to be inserted, or update the existing row or going down your defined reject link. If this is not happening then you need to submit it as a bug to your support provider.

I hope you are using SetNull() instead of what you posted.

This error means that your input value hasn't been read the way you expect. What is the datatype of this field where you are assigning "null"?

If your lookup data changes once a month, then it makes sense to create a lookup fileset and use that on each run. If the reference data changes daily or each run of the job you can save the resources required to build the lookup and do it directly. This is just one of several choices you can make. ...

Put the data down a separate link from a transform stage that is set to "abort after" 1 row.

You need to define "better". This could mean many things - for instance quickest runtime, or least impact on the database, or using the least system resources, etc.
How big is the table and how big is the source data in comparison? How often does the table change?

The format needs to be that of the source, I should have caught the 4 digit error, sorry. You can then choose your display options for the date elsewhere to display 4 digits.

You cannot put in any conditions to a copy stage, but you can use your proprosed logic in a transform stage to create 2 output links with different value and then join them together with a funnel.

The important change is in the format mask for the date, the substring is more efficient and versatile than the original convert() function but functionality is the same for your data.

Holy Keyboard Strokes, Batman - I didn't realize it was that many!

Does that mean it is present in the Palette? Can you see it in the Repository tree "Stage Types -> Parallel -> Database"? Is this a new installation or did it work previously?

Cool!

Your file is not fixed-width, your first data record has one less character than the second.
If you don't need the header or footer records I would strip them out and keep only the data records, using a filter of "sed '1d;$d'"