Lookup for VARCHAR or NVARCHAR in SSIS

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  July 15, 2015 at 4:42 am

  #300079

  Hi,

  I'm using SQL Server 2012. I'm doing a look up of a VARCHAR column. Though the values are inside table the lookups were always failing. When i looked into the profiler i got the below info.

  exec sp_executesql N'select * from (select * from [dbo].[Lookup]) [refTable]

  where [refTable].[Value] = @P1',N'@P1 nchar(255)',N'xyz '

  If we see the variable @P1 it says nchar. But the output from the excel /csv file it is NVARCHAR. I converted into VARCHAR to do the lookup. I tried between both VARCHAR and NVARCHAR - still it gives the same issue.

  Any help is highly appreciated.

  Thanks,

  Regards,

  Ami

• SQL.AP

  Hall of Fame

  Points: 3244

  More actions

  July 15, 2015 at 10:15 pm

  #1812840

  Try converting this column in SQL query instead of SSIS.

  Also, it would be of great help if you can elaborate your query properly.

  ____________________________________________________________

  AP

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  July 15, 2015 at 10:31 pm

  #1812841

  To create tables

  CREATE TABLE [dbo].[LookupSource](

  [id] [int] NULL,

  [value] [varchar](50) NULL

  ) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[Lookup](

  [Id] [int] NULL,

  [Value] [varchar](50) NULL

  ) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[LookupDestination](

  [id] [int] NULL

  ) ON [PRIMARY]

  GO

  Insert statements

  GO

  INSERT [dbo].[Lookup] ([Id], [Value]) VALUES (5, N'abc')

  GO

  INSERT [dbo].[LookupSource] ([id], [value]) VALUES (12, N'abc')

  GO

  INSERT [dbo].[LookupSource] ([id], [value]) VALUES (15, N'xyz')

  GO

  using the above tables, i'm creating a package which takes 'Value' from LookupSource and look for matches in Lookup table and inserting only the ids in LookupDestination. all values goes to Nomatchoutput. When I run the profiler and track the queries i could see the values column always comes as char instead of varchar. In the Lookup output columns etc i could see it as varchar.

  Any inputs?

  Thanks and regards,

  Ami

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  July 16, 2015 at 9:36 pm

  #1813162

  Hi,

  it started working when i connect with another machine which is having 2008. Not sure what was the problem with the previous machine which was having 2012. Need to check with the behaviour of 2012.

  Thanks and Regards,

  Ami

• SQL.AP

  Hall of Fame

  Points: 3244

  More actions

  July 16, 2015 at 9:51 pm

  #1813164

  Anamika (7/16/2015)

  ---

  Hi,

  it started working when i connect with another machine which is having 2008. Not sure what was the problem with the previous machine which was having 2012. Need to check with the behaviour of 2012.

  Thanks and Regards,

  Ami

  Cheers!..

  ____________________________________________________________

  AP

• maddukuru.rambabu

  Right there with Babe

  Points: 722

  More actions

  July 19, 2015 at 11:48 pm

  #1813571

  :-):-):-)