Hello there.
I've just upgraded from using DTS to SSIS. I've run through a few tutorials and am starting to use to the new ways of working.
There are however two task that I'm not really sure how to tackle, can someone suggest the best method in SSIS.
1) The destination table has many FK's. I'd like the package to check that the input data does not violate the FK's and use a default value for the columns where a violation occurs.
2) I need to increment a value in the package as a source column for use as the Primary Key field in the destination insert. It would be an added bonus if I could find out the maximum key used in the destination table already so that I can set my counter for this field at that value + 1.
Regards
Spangeman
New to SSIS - Destination FK's Lookup, auto increment for PK's
Naveen G
Well you've come to the right place! :)
Use a LOOKUP component to see if a value in the pipeline exists in some external table. You can send all rows that do not find a value down the error output from the LOOKUP component. You can then use a Derived Column component to add the default value into the pipeline. Join the 2 data=paths back together using a UNION ALL component and simply insert to the destination.
This technique is somewhat discussed here: http://www.sqlis.com/default.aspx 311 in method 2!
Try this: http://www.sqlis.com/default.aspx 37 and make sure you click on the "Row Number Transformation" hyperlink as well.
-Jamie