| Author |
Topic |
|
Nader
Starting Member
41 Posts |
 Posted - 2012-06-13 : 19:28:09
|
| Hello all,We are continuously receiving small xml files (every second) and we need continously to take this information and populate various tables the files should be processed in the sequence they arrive. On sorting the folder that contain those files the files are in the sequence they arrive.What is the best way to do that.I tried to take the information and put it in temporary table then delete it every second this was not good.Is there a way to save the information in the xml file after using xslt in variables then run sql task that will populate the various tables using the values in those variables.Appreciate your help and suggestion. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-13 : 20:48:51
|
| this is totally possible. do you have a staging database where you are doing a full dump of the xml data? is there a unique value that identifies each row? if you do have a staging database can you provide schema for one of your staging tables? also please provide corresponding xml file sample.<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
Nader
Starting Member
41 Posts |
Posted - 2012-06-14 : 12:55:59
|
| What is meant by full dump This is not a huge xml those are very small xml files that arrive every second continuously and should be prcessed in the order they arrive.Thanks |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-14 : 13:49:01
|
| full dump means copying the contents of the xml files into staging tables<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-06-15 : 15:00:14
|
| How can I do that for thousand xml files at one time?Thankssarah |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-15 : 17:12:06
|
can the xml files have identical name?1. create a loop container in your ssis2. point it to folder of xml location3. ingest to staging table4. push to destination database and mark each row pushed to destination as processed with isProcessed = 1 5. delete rows processed with isProcessed column value of 16. attach ssis to a sql job that runs every second ( would not recommend that because it won't run anyways if the process is running in background)  <><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
Nader
Starting Member
41 Posts |
Posted - 2012-06-18 : 11:30:20
|
| I did something like that before but did not set the task to run every so and so seconds But I am worried about the performance if that task is running continously at the backend and truncating staging tables every second or nanosecond. I test it by running it from ssis package. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-18 : 12:28:21
|
| does xml filenames have unique pattern to determine order/sequence? for the staging table if amount of data is very small you can do clearing up less frequently. just add filename or id onto table as a field in that case to identify data corresponding to one file. Also you can have an additional control table to log details of files picked up and processed by package and also result so that next time it will only consider files which are not already in that table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-18 : 14:21:26
|
| then you can remove the truncating and use the isProcessed column to keep track of your processed data<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
importing info from xml small files every second