January 6, 2011 at 4:59 am
Hello,
I need to implement the following functionality in SSIS-
If the value for a column(POL_COUNTRY_CD) in a table(NCV_BL_DHL_TEMP) begins with 'FR', then join to another table (MG_Location). In the MG_Location table, if the value for the column(Continent_CD) is 'EUR', then lookup 'FRN' in the static table(DHL_Assignment) else if the value is 'MED' then lookup 'FRS'.
-----------------------------------------------
Table structure for NCV_BL_DHL_TEMP
-----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM] [nvarchar](13) NULL,
[CP_GROUP_CD] [nvarchar](30) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL,
PRIMARY KEY CLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_BL_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_BL_DHL_TEMP
values('6525833', 'MAA012330', 'SAIMA', 'IN', 'FR', 'HCMB', '0083W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('7102907', 'SH8634919', 'AL_FILTER', 'GB', 'IN', 'XYKOU', '0058W', 'W', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6117626', 'GLA007219', 'HANKYU', 'AU', 'AT', 'HANB', '01E', 'E', '02-02-2010', '1');
insert into NCV_BL_DHL_TEMP
values('5772271', 'BLR009972', 'TTP_TECHNOLOGIE', 'AT', 'AU', 'HJCH', '0036W', 'W', '04-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('8235799', 'NTG100115', 'SHIPCO', 'ES', 'TR', 'YMUNS', '12W', 'W', '02-06-2009', '2');
insert into NCV_BL_DHL_TEMP
values('6137996', 'FXT128719', 'FELIANCE_FIBRES', 'FR', 'ES', 'CNTSNG', '258E', 'E', '01-09-2010', '4');
insert into NCV_BL_DHL_TEMP
values('6168340', 'ANR490955', 'DHL_DANZAS', 'BR', 'CA', 'CSGNZU', '006E', 'E', '02-08-2010', '2');
insert into NCV_BL_DHL_TEMP
values('6168346', 'ANR440555', 'DHL_DANZAS', 'CA', 'BR', 'CSGWZU', '006E', 'E', '02-01-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HK1016941', 'OKI', 'CN', 'GB', 'YPULT', '03W', 'W', '02-02-2007', '2');
insert into NCV_BL_DHL_TEMP
values('6581127', 'HK5010911', 'KOK', 'AG', 'IS', 'YMULT', '03W', 'W', '04-06-2008', '2');
insert into NCV_BL_DHL_TEMP
values('6581123', 'HD1086941', 'KPP', 'IS', 'AG', 'YKULT', '03W', 'W', '02-05-2009', '2');
------------------------------------------------------------------------------------
The structure for the DHL_TRADE_ASSIGNMENT table-
-----------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
UNIQUE NONCLUSTERED
(
[COUNTRY_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------
Sample data for DHL_TRADE_ASSIGNMENT-
-----------------------------------------------------------------------
insert into DHL_TRADE_ASSIGNMENT
values('BR', 'Brazil', 'AMLA', 'ECSA');
insert into DHL_TRADE_ASSIGNMENT
values('TR', 'Turkey', 'EMA', 'EAST MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRS', 'France South', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRN', 'France North', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('AU', 'Australia', 'SPAC', 'SPAC');
insert into DHL_TRADE_ASSIGNMENT
values('IN', 'India', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('RO', 'Romania', 'BLACK SEA', 'BLACK SEA');
insert into DHL_TRADE_ASSIGNMENT
values('CA', 'Canada', 'AMNO', 'AMNO');
insert into DHL_TRADE_ASSIGNMENT
values('AG', 'Antigua', 'AMLA', 'NCSA');
insert into DHL_TRADE_ASSIGNMENT
values('IS', 'Iceland', 'EURO NC', 'OTHERS EURO');
insert into DHL_TRADE_ASSIGNMENT
values('EC', 'EC', 'AMLA', 'WCSA');
insert into DHL_TRADE_ASSIGNMENT
values('CN', 'China', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('GB', 'Great Britain', 'EURO NC', 'EURO NC');
2nd table
MG_LOCATION
-----------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MG_LOCATION](
[LOCATION_CD] [varchar](5) NOT NULL,
[LOCATION_DSC] [varchar](35) NULL,
[STATE_CD] [varchar](3) NULL,
[COUNTRY_CD] [varchar](2) NOT NULL,
[CONTINENT_CD] [varchar](3) NULL,
CONSTRAINT [PK_MG_LOCATION] PRIMARY KEY CLUSTERED
(
[LOCATION_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--------------------------------------------------------------------
Sample data for MG_LOCATION
-----------------------------------------
insert into MG_LOCATION
values('FRTAR', 'TARNOS', 'B', 'FR', 'MED');
insert into MG_LOCATION
values('FRTCC', 'TOCANE-SAINT-APRE', 'B', 'FR', 'EUR');
insert into MG_LOCATION
values('FRTCN', 'TUCHAN', 'C', 'FR', 'MED');
insert into MG_LOCATION
values('GBCNE', 'CALNE', 'D', 'FR', 'EUR');
insert into MG_LOCATION
values('IT2QW', 'CASTEL CONDINO', 'E', 'IT', 'EUR');
------------------------------------------------------------
I hope the aboe information would help.
Thanks in advance for your help.
Kind Regards,
Paul
January 6, 2011 at 5:35 am
you could do this using a conditional split, or even by using multiple SQL source statements doing the join.
January 6, 2011 at 5:38 am
Hi Steve,
Thanks for your reply. However, I am very new to SQL and SSIS socould you please elaborate on what you mentioned ?
Kind Regards,
Paul
January 6, 2011 at 6:56 am
in an overview, you could either have one source sql statement for your data flow and then in the data flow split the data out based on the criteria that you outlined.
or alternatively, you could create seperate sql source statements that join the tables using an inner join and a relevant where clause.
which option you choose would depend on where the data is stored, if your are reading from the same database for all the tables i would tend to go for option two,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply