November 10, 2010 at 12:39 pm
Looking for the concept here not a detailed solution. I'm fairly new to using Integration Services in MSSQL 2005.
I have 7 years worth of student data in individual sql server databases (imported for Access) with 107 tables each. I want to import all tables and rows into a single database but I want the data ignored if it violates the primary key constraints.
Is there a way to ignore the import data without stopping the package with an error.
Thanks
November 10, 2010 at 12:57 pm
You could perform a lookup to the destination table on the primary key, and then only insert records that fail the lookup. You could even redirect your records that lookup into a table and then you'll have a collection of all of your duplicate records if you want.
November 10, 2010 at 1:02 pm
Two approaches.
Entirely within SSIS: Use multiple sources, one your flat file(s), the other the target table in the database. Bring them both through a Merge Join to detect for existance. Then conditional split them so only the unfound data is pushed to the table.
Another option (personally preferred): Dump each file to a staging table, use standard T-SQL in a proc to do the rest, truncate when done to prep for the next file. This lets you at least examine the results in the middle in case of failures.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 12, 2010 at 6:51 am
Thanks to the both of you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply