title: Combining OLE DB lookup with flat file SSIS
body: <p>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 :(</p>
  
  <p>Here's  my design:</p>
  
  <p><img src="https://i.stack.imgur.com/gGmw0.jpg" alt="enter image description here"></p>
  
  <p>When I debug i get the message:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>ObjectId is an int that I am selecting inside the Lookup. The whole query for the lookup is:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Update 1</strong> (see @billinkc answer)</p>
  
  <p>New design:</p>
  
  <p><img src="https://i.stack.imgur.com/LwwOy.jpg" alt="enter image description here"></p>
  
  <p>New Error :(</p>
  
  <pre><code>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)
  </code></pre>