Huge Xml Document(more than 10GB) import to Sql Server 2005
-
i have to import xml document having size more than 10GB in Sql Server database. i also have to do XSD Validation before Processing and maintain transaction. Please suggest me the best way to do the same. should i use SSIS packege? is it capable of Processing such large document? or Do i have to use c# coding to accomplish this task?
-
i have to import xml document having size more than 10GB in Sql Server database. i also have to do XSD Validation before Processing and maintain transaction. Please suggest me the best way to do the same. should i use SSIS packege? is it capable of Processing such large document? or Do i have to use c# coding to accomplish this task?
-
you can do this using SSIS package. Please check this
♫ 99 little bugs in the code, 99 bugs in the code We fix a bug, compile it again 101 little bugs in the code ♫
-
i have to import xml document having size more than 10GB in Sql Server database. i also have to do XSD Validation before Processing and maintain transaction. Please suggest me the best way to do the same. should i use SSIS packege? is it capable of Processing such large document? or Do i have to use c# coding to accomplish this task?
I suggest you proceed cautiously. That is a big file and the vast majority of solutions deal with smaller files. If it was me I think I would do the following 1. Create an app that does nothing but divide it into smaller files. Might not even do validation. 2. Create a second app that consumes the smaller files, validates, logs the file processed and then post to the database. Advantages in the above. 1. If an error occurs you are going to need to manually review it. And reviewing a file that is 10/100 meg is easier than 10 gig. 2. If an error occurs you can manually fix and continue with the file that errored, rather than restarting everything. 3. You don't need to deal with the issue that the file is just too big for some part of the process. Regardless however you must have the following features. 1. Log as you complete each block (insert, whatever) of work so you not only know where something failed but also can track that it succeeded. 2. Insure that transaction blocks are small. 1000 is probably a good number. 3. Design for the possibility that something will fail so you have a way to restart it at the failure point.
-
I suggest you proceed cautiously. That is a big file and the vast majority of solutions deal with smaller files. If it was me I think I would do the following 1. Create an app that does nothing but divide it into smaller files. Might not even do validation. 2. Create a second app that consumes the smaller files, validates, logs the file processed and then post to the database. Advantages in the above. 1. If an error occurs you are going to need to manually review it. And reviewing a file that is 10/100 meg is easier than 10 gig. 2. If an error occurs you can manually fix and continue with the file that errored, rather than restarting everything. 3. You don't need to deal with the issue that the file is just too big for some part of the process. Regardless however you must have the following features. 1. Log as you complete each block (insert, whatever) of work so you not only know where something failed but also can track that it succeeded. 2. Insure that transaction blocks are small. 1000 is probably a good number. 3. Design for the possibility that something will fail so you have a way to restart it at the failure point.
I have 2GB RAM in my machine. i tried SSIS package for 1.5GB file it's giving error. Error: 0xC02092AF at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to process the XML data. Insufficient memory to continue the execution of the program. If SSIS is not right choice than i have to follow the process as jschell suggest. can anyone give me some code sample for splitinf file into smaller one. i can split 10GB file into 10 1GB smaller files not more. If i process 10 1GB files than how can i maintain transaction in all files? i mean to say if 1 file fails all others files should be rollbacked.
-
I have 2GB RAM in my machine. i tried SSIS package for 1.5GB file it's giving error. Error: 0xC02092AF at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to process the XML data. Insufficient memory to continue the execution of the program. If SSIS is not right choice than i have to follow the process as jschell suggest. can anyone give me some code sample for splitinf file into smaller one. i can split 10GB file into 10 1GB smaller files not more. If i process 10 1GB files than how can i maintain transaction in all files? i mean to say if 1 file fails all others files should be rollbacked.
-
I suggest you proceed cautiously. That is a big file and the vast majority of solutions deal with smaller files. If it was me I think I would do the following 1. Create an app that does nothing but divide it into smaller files. Might not even do validation. 2. Create a second app that consumes the smaller files, validates, logs the file processed and then post to the database. Advantages in the above. 1. If an error occurs you are going to need to manually review it. And reviewing a file that is 10/100 meg is easier than 10 gig. 2. If an error occurs you can manually fix and continue with the file that errored, rather than restarting everything. 3. You don't need to deal with the issue that the file is just too big for some part of the process. Regardless however you must have the following features. 1. Log as you complete each block (insert, whatever) of work so you not only know where something failed but also can track that it succeeded. 2. Insure that transaction blocks are small. 1000 is probably a good number. 3. Design for the possibility that something will fail so you have a way to restart it at the failure point.
Your reply to this topic has not just helped him... :-D I never thought of divide and conquering files for those advantages, thanks!
-
Your reply to this topic has not just helped him... :-D I never thought of divide and conquering files for those advantages, thanks!