I have to import 9 csv files using SSIS. These files are having primary keys, foreign keys. While importing the files in package, how can I maintain these constraints on my target SQL tables?
Any ideas?
Should I split the work in 2 stages
Stage 1 - directly import raw data into temp tables
Stage 2 - copy from temp tables to target SQL tables and then create the PK, FK on target tables.
I'm a proponent of staging the data in staging tables prior to putting it in the target table/format. If you were absolutely sure that the constraints from the source system will never have data integrity problems then you could go directly into your target table and structure with the constraints applied. It does offer a bit more flexibility to stage the data, but also takes a little more work. If you encountered integrity issues in the data, then a staging environment would allow you to clean the data and would allow the non-offending data rows to be populated. Without a staging table, you would be forced into scrubbing the flat files, which can be painful...especially with a large data file that is difficult to open in conventional text editors.
sql serverthen later alter tables inDB?? have you tried this - vhadalgi