refrence table

Question

hi,

in my master table i have 1 column which has values 1,2

example :

type

1

2,

now i have referenct table

type code

1                  employee

2                 department

i am loading my data into master table thorugh ssis,

hwo to i check for values that should be from reference table only.

Answer 1

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)
SELECT 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

Answer 2

Lookup 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

Answer 3

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.

or

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

Answer 4

this is not what i want

i have 2 table

master

empid  empname

1          smith

2          john

3             rob

reference for empid will be

emp_ref

empid empdesc

1              it

2             civil

3             designer

now in my master table how i write check constraint which also accept null

Answer 5

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.

Answer 6

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

Answer 7

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:

http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx

---

Arthur My Blog

Answer 8

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)

Answer 9

A check constraint is different than actually inserting the values.

Here are articles on the concepts of check constraints and on how to create check constraints. Note that NULLS can override a check constraint.

---

MCITP:DBA, MCDBA, MCSA