Back in the day, I used BCP to my heart's content to load data into my tables. Later on with DTS and SSIS, I changed modes. Now we have XML files coming in which are pretty much going straight to the database as is. The daily data load is about 4 MB of data divided into three tables a day. Tibco is being used to download the files and is capable of triggering a stored procedure to run the import. The options that I can think of are: 1. SSIS with SQL Job and use sp_execute_job 2. Custom stored procedure that takes the full XML as input and loads to table I have a two part question: 1. Are there any other options that I should consider? 2. What are the merits / demerits of these options?
Maybe it's because I've been on an ETL tear for the last couple of weeks, but I would use SSIS for this, at least as long as [the XML file is relatively simple]. It will give you additional error handling logic, which is definitely worth the minor cost in extra development time to me. It's also easier to be flexible: if one row fails, you can decide whether to fail the whole thing (and roll everything back) or just output the row into a failed listing and alert somebody who cares so they can fix the problem. That's almost trivial in SSIS but a bit more difficult to do in T-SQL. That said, either option would work just fine. T-SQL XML shredding has gotten to be pretty good, so if you went down that route, you could certainly fashion something that works just fine. An alternative to using sp_execute_job might be to have your package start up and search for the XML files. This works best if you receive the XML data via Tibco at a regular interval. If it's always there every day at 9 AM, for example, you can have the SSIS package start scanning a few minutes before and wait maybe 10 minutes. If the files come in at unpredictable times, though, triggering the job would be better. Other than that, I can't think of any products or options which would be substantially easier. This is right in the wheelhouse for SSIS and there's no reason you couldn't do it in T-SQL, either. :