Q:

SQL Data load - Suggestions required

Gurus,
We are in process of setting up a SSIS package to load a formatted text file in to SQL server. It will have around 100 million rows and file size will be (multiple files of around 15 GB each) 100 GB. The file format is aligned with XML schema like mentioned below... it takes nearly 72 hrs to load this file in to SQL server tables... 
File format
EM|123|XYZ|30|Sales mgr|20000|AD|1 Street 1| State1|City1|US|AD|12Street 2|state 2|City2|UK|CON|2012689648|CON|42343435
EM|113|WYZ|31|Sales grade|200|AD|12 Street 1| State2|City2|US|AD|1Street 22|state 3|City 3|UK|CON|201689648|CON|423435
EM|143|rYZ|32|Sales Egr|2000|AD|113Street 1| State3|City3|US|AD|12Street 21|state 4|City 5|UK|CON|201269648|CON|443435
Data will come in above format. It means "EM" till "AD" is Employee details like Code,Name,age,Designation,Salary and "AD" is Address details like Street,Sate,City,Country. Address data can be multiple for same employee...similarly "CON" is contact details with Phone number which also may be multiple.
So, we need to load Employee Details in to seperate table, Address details in seperate table and Contact details in seperate table with Code as Primary key in Employee Details and Reference key in other two tables.
We designed package like, had a Script Component as Source and parsed line by line by using .NET scripts and created multiple out put buffers each per table and added the row in the script. Mapped the Script component output to 3 OLE DB Destinations (SQL Server tables).
Our server is Quad Core with 48 GB RAM virtualized and we have 2 cores with 24 GB dedicated for DB. Our SQL server DB (Simple Recovery model) has Data files in Network share location that is SAN storage. To improve performance we created Each table in differenct data file (Primary and secondary).. but still it takes around 72 hrs.
Need guidance on following points.

Is it possible to use BCP, if yes any pointers.. (Hope BCP will perform better)
Any suggestions on specified solution.
Any alternates...

There are no indexes defined on the table also no triggers...We have even set defaultMaxbufferzie to 100 MB
Looking forward for response..Any help is much appreciated..

A:

1.) If necessary, simplify/flatten your XML file via XSLT as shown here:
http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx
2.) Use XML Source as shown here:
http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx
3.) Drop any indexes on the destination tables
4.) If your source data is bulletproof, disable constraints on the tables via:
ALTER TABLE [MyTable] NOCHECK CONSTRAINT ALL

5.) Load the data via OLEDB Destination
6.) Re-enable constraints
7.) Re-create indexes

