Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-02-16 : 12:40:36
|
| I want to set up a process to import XML data using the SQLXMLBulkLoad object.This will basically just bang XML data into staging tables.Problem is that I need to know what constitutes a "batch".So if an import takes 10 minutes, and another one starts concurrently 5 minutes after the first, I need to assign a batch number separately for the two processes.Then I can have a follow-on task process the data in the staging tables, but be sure to only process batches that are completely imported (so that the referential integrity of the data is fully resolved by virtue of the bulk importing finishing).I can't see a way to influence the data that SQLXMLBulkLoad puts into the database - it will be what's in the XML and that's that.So I'm left with using the SPID of the connection, possibly, as an indicator for the batch.SQLXMLBulkLoad uses a conventional OLEDB connection, so I could call an SProc before & after running the SQLXMLBulkLoad - which presumably would use the same SPID and could thus "mark" all the rows in the staging tables with that SPID with a "proper" batch number?Second idea: have unique staging table names for each batch. To do this I will have to generate a custom Mapping Schema File for each batch - which is a bit of a pain.Third idea: Pre-process the XML to add a GUID to the <ROOT> tag so that all child-elements could inherit that. That means I can't stream the XML from an external source, I'll have to stream it into a file first, and then modify the <ROOT> tag, and then import it with SQLXMLBulkLoad.Any other ideas?ThanksKristen |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-17 : 13:51:40
|
| Any ideas please?Kristen |
 |
|
|
|
|
|
Need Batch Number for Bulk import process