September 22, 2008 at 12:13 pm
I am having an issue with foreign keys at the moment. I am doing a DataReader Source transfer from my current database to a different set of tables for an excel insert.
Here is my setup currently:
DataReader Source > Data Conversion > SQL Server Destination
** Works fine. This is the insert to the parent table, the PK is an IDENTITY.
DataReader Source > Data Conversion > Lookup (for the parent table) > Conditional Split > 3 SQL Server Destinations
** The lookup is giving me an error saying nothing matched. Without the lookup this insert works fine except the foreign key is not being recognized in any of the 3 tables, which is the IDENTITY from the parent table. I have the foreign key constraints set up correctly but I am still having this issue for some reason.
Here is my table create statement:
CREATE TABLE [dbo].[temp_ameriflex_detail](
[ameriflex_id] [int] IDENTITY(1,1) NOT NULL,
[emp_ssno] [nvarchar](11) NULL,
[emp_lname] [nvarchar](25) NULL,
[emp_fname] [nvarchar](25) NULL,
[emp_mi] [nvarchar](25) NULL,
[emp_add1] [nvarchar](50) NULL,
[emp_add2] [nvarchar](50) NULL,
[emp_city] [nvarchar](25) NULL,
[emp_state] [nvarchar](2) NULL,
[emp_zip] [nvarchar](25) NULL,
[emp_gender] [nvarchar](10) NULL,
[emp_doh] [nvarchar](20) NULL,
[emp_dob] [nvarchar](20) NULL,
[emp_home_phone] [nvarchar](20) NULL,
[deduc_no_of_deduc] [numeric](18, 0) NULL,
CONSTRAINT [PK_temp_ameriflex_detail] PRIMARY KEY CLUSTERED
(
[ameriflex_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]
CREATE TABLE [dbo].[temp_ameriflex_dep](
[dep_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[dep_care_ben_id] [nvarchar](20) NULL,
[dep_care_per_amt] [numeric](18, 0) NULL,
[dep_care_ann_amt] [numeric](18, 0) NULL,
CONSTRAINT [PK_temp_ameriflex_dep] PRIMARY KEY CLUSTERED
(
[dep_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]
GO
ALTER TABLE [dbo].[temp_ameriflex_dep] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_def_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_dep] CHECK CONSTRAINT [FK_temp_ameriflex_def_temp_ameriflex_detail]
CREATE TABLE [dbo].[temp_ameriflex_med](
[med_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[med_rem_ben_id] [nvarchar](20) NULL,
[med_rem_per_amt] [numeric](18, 0) NULL,
[med_rem_ann_amt] [numeric](18, 0) NULL,
CONSTRAINT [PK_temp_ameriflex_med] PRIMARY KEY CLUSTERED
(
[med_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]
GO
ALTER TABLE [dbo].[temp_ameriflex_med] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_med_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_med] CHECK CONSTRAINT [FK_temp_ameriflex_med_temp_ameriflex_detail]
CREATE TABLE [dbo].[temp_ameriflex_card](
[card_id] [int] IDENTITY(1,1) NOT NULL,
[ameriflex_id] [int] NULL,
[card_ben_id] [nvarchar](20) NULL,
[card_per_amt] [numeric](18, 0) NULL,
[card_ann_amt] [numeric](18, 0) NULL,
CONSTRAINT [PK_temp_ameriflex_card] PRIMARY KEY CLUSTERED
(
[card_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]
GO
ALTER TABLE [dbo].[temp_ameriflex_card] WITH CHECK ADD CONSTRAINT [FK_temp_ameriflex_care_temp_ameriflex_detail] FOREIGN KEY([ameriflex_id])
REFERENCES [dbo].[temp_ameriflex_detail] ([ameriflex_id])
GO
ALTER TABLE [dbo].[temp_ameriflex_card] CHECK CONSTRAINT [FK_temp_ameriflex_care_temp_ameriflex_detail]
** If anyone can help it would be greatly appreciated.
September 22, 2008 at 12:34 pm
If the lookup is telling you that it found no matches, trust it. It found no matches.
By looking at your DDL, I would guess that you have a VARCHAR in your source data and an NVARCHAR in the destination table. These will not yield a match. SSIS is very type-sensitive, it is also case-sensitive and trailing-space sensitive. Make sure your joining field for your lookup is EXACTLY the same data type, case, and does not have any trailing or leading spaces causing you a problem.
September 22, 2008 at 12:48 pm
I changed all my datatypes to VARCHAR (that needed to change) and I still get an error on my lookup that says it is yeilding no matches.
September 22, 2008 at 12:51 pm
RTRIM() or TRIM() everything. Trailing spaces get in the way a lot.
September 22, 2008 at 12:54 pm
Hi,
If you have ole db destination, then there is keep identity checkbox in editor. This will also copy the identity property from source. Why r you using datareader source ?
VG
September 22, 2008 at 12:56 pm
There shouldn't be an issue with int and numeric communicating should there? Just another idea to throw out there.
September 22, 2008 at 12:59 pm
Don't join an INT to a NUMERIC in a lookup. They may not always match - especially if you are using an Oracle or Sybase data source. Use a derived column to data conversion to get the types to match the lookup types.
September 22, 2008 at 1:05 pm
VG (9/22/2008)
Hi,If you have ole db destination, then there is keep identity checkbox in editor. This will also copy the identity property from source. Why r you using datareader source ?
VG
I am using a DataReader Source because I have to import a certain query into my tables.
I also changed the numeric datatypes to int to match the tables and still nothing works, as well as the TRIM functions. Same error always pops on the Lookup.
September 22, 2008 at 1:11 pm
While running in debug mode, have you put a data viewer on the data flow into the lookup to see what data is there? If not, this would be a good time to try it. You can then verify the lookup using SSMS to make the same query based on the data you see.
😎
September 22, 2008 at 1:25 pm
I added a Data Viewer between Data Conversion and Lookup and the information is correct, I just don't know what is causing the problems on the lookup.
September 22, 2008 at 1:55 pm
I changed the Fail Component in the Lookup to Redirect Rows and now the lookup goes through correctly. The data is going into the tables but the foreign key column are all NULL values now.
So apparently doing this fixed the error but the foreign key is still NULL.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply