Wednesday, November 21, 2012 2:45 PM
in my master table i have 1 column which has values 1,2
now i have referenct table
i am loading my data into master table thorugh ssis,
hwo to i check for values that should be from reference table only.
Wednesday, November 21, 2012 3:07 PM
Not entirely sure what your question is. But if you're trying to only load data into the master table where the type already exists in the reference table, then you have multiple options. If the data is all in SQL Server already, just use an Execute T-SQL to do a MERGE or INSERT (with a JOIN) statement. The below code will auto filter out anything that doesn't match in reference.
INSERT INTO dbo.MasterTable (Type)
FROM dbo.MyOtherTable mot
INNER JOIN dbo.Reference r
ON mot.Type = r.Type
If you're loading from a file or a non-SQL Server source, in your Data Flow Task you can use a Conditional Split Transformation to send the good data to SQL Server and either send the bad data to an error table or ignore the bad data or do "other things" with it. Some people use the Lookup transformation, but I'm not a fan of it since it gives such a performance hit (or does in 2005, anyway). I haven't worked with Term Lookup, so I'm not sure if that would work or not.
Lastly, you could just load the file into a staging table and use an Execute T-SQL Task with a stored procedure / view / above JOIN code to do your final insert.
MCITP:DBA, MCDBA, MCSA
Wednesday, November 21, 2012 3:07 PMLookup transformation. http://msdn.microsoft.com/en-us/library/ms141821.aspx
Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com
Wednesday, November 21, 2012 5:17 PM
do an ignore failure in look up when no match to refrence .entire data will be inserted to ur destination.just do
select * from [table name] where id_column is null. this will give u set of records where u did nt had values to reference.
u can redirect the rows on lookup no match to flat file.these will b records which are not in reference. and hence inserted records will be from reference
- Edited by Nishink Wednesday, November 21, 2012 5:18 PM
Wednesday, November 21, 2012 5:29 PM
this is not what i want
i have 2 table
reference for empid will be
now in my master table how i write check constraint which also accept null
Wednesday, November 21, 2012 6:05 PM
check constraint on master table for checking the ids of emp_ref table?
if u want ur master table to have only those values which are there in ur reference then u can create a Pk for id in reference table
and foreign key constraint on master table .so u have to have a value in reference table then only u can enter the value in master table.
but then u cannot enter nulls.
Wednesday, November 21, 2012 6:15 PM
i have done pk->fk
but now in my master which has fk ,some of the values coming from file are blank,
which failed my package.
i am allowing foreign key to have null values
Wednesday, November 21, 2012 6:15 PMModerator
Just do two hops: one to pick only those matching records through an inner join and the other to flag off these that did not match (no x-ref to id).
You are trying to sync tables, one approach is using the T-SQL MERGE clause, quite easy IMHO, or you can go with a pure SSIS approach:
Arthur My Blog
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Wednesday, November 28, 2012 2:17 AM
Wednesday, November 21, 2012 6:32 PM
Since u dont want to use look up.
what u can try is .create a dummy value in ur refernce table for null say 100 (or some value.Best is populate 1st value for null)so that 100 signifies for null.
and when u load the data via ssis u can make all the null records as 100 and insert.(its a workaround just for not failing the package)
- Edited by Nishink Wednesday, November 21, 2012 6:32 PM
Monday, November 26, 2012 11:57 AM