September 4, 2018 at 6:11 am
Hi,
I have this table
CREATE TABLE [dbo].[D_TBL1](
[PK_TBL1] [int] IDENTITY(1,1) NOT NULL,
[TBL1_ID] [nchar](3) NOT NULL,
CONSTRAINT [PK_D_TBL1] PRIMARY KEY CLUSTERED
(
[PK_D_TBL1]
ASC
)
) ON [PRIMARY]
I'm building a Dataflow Task and I have the following Table
CREATE TABLE [dbo].[D_TBL2](
[PK_TBL2] [int] IDENTITY(1,1) NOT NULL,
[TBL2_ID] [nchar](3) NOT NULL,
[FK_TBL1] [int] IDENTITY(1,1) NULL,
CONSTRAINT [PK_D_TBL2] PRIMARY KEY CLUSTERED
( [PK_D_TBL2]
ASC
)
) ON [PRIMARY]
As it goes, in my Data Flow TBL2_ID has already gone a long way and been read from the source.
The LUT Task should achieve the following:
For each provided TBL2_ID = TBL1_ID, lookup PK_TBL1 and add it as new column named FK_TBL1
So the LUT Looks like this:
Mappings: Input Column TBL2_ID, Lookup Column TBL1_ID
Lookup Column PK_TBL1, Lookup Operation: add as new column, Output Alias: FK_TBL1
Now this seems to work for all TBL1_IDs which have 3 Digits e.g. 123, but when it get's to a 2 digit value e.g. 09 it seems to fail. Do all values need to have 3 digits for this to work?
September 4, 2018 at 6:20 am
DinoRS - Tuesday, September 4, 2018 6:11 AMHi,
I have this table
CREATE TABLE [dbo].[D_TBL1](
[PK_TBL1] [int] IDENTITY(1,1) NOT NULL,
[TBL1_ID] [nchar](3) NOT NULL,
CONSTRAINT [PK_D_TBL1] PRIMARY KEY CLUSTERED
(
[PK_D_TBL1]ASC
)
) ON [PRIMARY]
I'm building a Dataflow Task and I have the following Table
CREATE TABLE [dbo].[D_TBL2](
[PK_TBL2] [int] IDENTITY(1,1) NOT NULL,
[TBL2_ID] [nchar](3) NOT NULL,
[FK_TBL1] [int] IDENTITY(1,1) NULL,CONSTRAINT [PK_D_TBL2] PRIMARY KEY CLUSTERED
( [PK_D_TBL2]
ASC
)
) ON [PRIMARY]As it goes, in my Data Flow TBL2_ID has already gone a long way and been read from the source.
The LUT Task should achieve the following:
For each provided TBL2_ID = TBL1_ID, lookup PK_TBL1 and add it as new column named FK_TBL1
So the LUT Looks like this:
Mappings: Input Column TBL2_ID, Lookup Column TBL1_ID
Lookup Column PK_TBL1, Lookup Operation: add as new column, Output Alias: FK_TBL1Now this seems to work for all TBL1_IDs which have 3 Digits e.g. 123, but when it get's to a 2 digit value e.g. 09 it seems to fail. Do all values need to have 3 digits for this to work?
As it's a char column, I suspect you'll need to lookup on '09 ' rather than '09'. Don't think of them as digits, think of them as strings – that is how SQL Server is treating them.
How are you defining your lookup – are you using a query as the lookup source, or the table?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 4, 2018 at 7:03 am
The lookup is on the table
September 4, 2018 at 11:02 am
DinoRS - Tuesday, September 4, 2018 7:03 AMThe lookup is on the table
Unless your lookup table is unfiltered and contains only two columns, you should be using a SELECT query to populate the lookup (because this data is (by default) loaded into RAM, for performance reasons).
One way or another, you need to make the spaces, or lack thereof, match up on both sides.
The first thing I'd try is setting up your lookup query as follows:select TBL1_ID = cast(TBL1_ID as varchar(3)), PK_TBL1
from dbo.D_TBL1
and see whether that helps.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 6, 2018 at 8:17 am
I was able to change the columns from nchar to int, no more trailing spaces 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply