Warning: I have no experience in SSIS, and not much in SQL Server, so this may be a stupid question.

I am tasked with updating a table in a database on a different server, on a regular basis. I have followed the Stairway to Integration Services articles on SQLServer Central, and I think I have designed it correctly, but I have a problem with the lookup to identify which rows should be updated.

The difficulty is that I have 2 fields in my source data (TaxOfficeNumber nvarchar(3) and PAYERef nvarchar(50)) which correlate to a single field EmployerPAYEReference varchar(256) in the destination table. In addition, the destination fields have lost any leading zeroes. So '0123' and 'MYREF' in my table should link to '23/MYREF' in the destination.

I'v managed to cast everything so the same format and datatype, but I can't connect the fields in the Lookup.

My source looks like:

SELECT Submitter_Ref_Number
      , Transmission_DateTime
      , MessageStatus
      , HMRC_UniqueID
      , HMRC_Ack_DateTime
      , Product_Name
      , CAST(CAST(CAST(TaxDistrictNumber AS int) AS varchar(3)) + '/' + PAYERef AS varchar(50)) AS EmployerPAYReference 
FROM dbo.P11D

and the lookup looks like:

SELECT [EmployerPAYEReference]
  FROM [P11D].[dbo].[hmrc_test]

In the Lookups editor, when I try to link the columns, I get an error: cnnot match the input column to te lookup column because the data types do not match.

I've tried various options and got nowhere, so any suggestions?

Using SQL Server 2008 R2