Combining OLE DB lookup with flat file SSIS

Question

I have the need to read rows from a set of csv files and write them into an existing table in my database. In order to do this however, I need to combine the flat file data with 2 selected values from a table in my database. I have constructed a design in SSIS that should allow this, using a Lookup transformation. However, it's not working :(

Here's my design:

When I debug i get the message:

Error at Contacts [SSIS.Pipeline]: input column "ObjectId" (914) has lineage ID 709 that was not previously used in the Data Flow task.
Error at Contacts [SSIS.Pipeline]: "component "OLE DB Destination" (134)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at Contacts [SSIS.Pipeline]: One or more component failed validation. 
Error at Contacts: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

ObjectId is an int that I am selecting inside the Lookup. The whole query for the lookup is:

DECLARE @ObjectId int
Select @ObjectId = (SELECT TOP (1) [Value] as ObjectId
FROM  dbo.Sequences WHERE (Name = 'Contact'));
UPDATE Sequences SET Value = Value + 1 WHERE (Name = 'Contact');
SELECT ObjectId = @ObjectId, Reference = N'CU' + cast(@ObjectId as VARCHAR(20))

This query works perfectly in preview. Can someone please tell me what I'm doing wrong? Apologies for my ignorance but this is the first thing I've done in SSIS.

Update 1 (see @billinkc answer)

New design:

New Error :(

Error at Contacts [Lookup Sequence [531]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Syntax error, permission violation, or other nonspecific error".
Error at Contacts [Lookup Sequence [531]]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties.
Error at Contacts [SSIS.Pipeline]: "component "Lookup Sequence" (531)" failed validation and returned validation status "VS_ISBROKEN".
Error at Contacts [SSIS.Pipeline]: One or more component failed validation.
Error at Contacts: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)

Answer 1

Your lookup should be inline after the Generate EntityGUID step and not a union. But, looking at your Lookup code, that's not how a Lookup is going to work. A lookup is a read-only type of operation

Instead, you'll want to change that out to an OLE DB Command object.

Finally, that SQL code you have to generate those IDs. That's... That could be done more efficiently, if you're interested.