Re: Using a XML source file with DTS?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 06:40:17 +0000

In message <e0keUXWBEHA.916@tk2msftngp13.phx.gbl>, John Peterson
<j0hnp@comcast.net> writes
>(SQL Server 2000, SP3a)
>
>Hello!
>
>I'm wondering if there are any examples that show how I can process a
>fairly simple XML
>document as an input connection (sort of like a text file or an Excel
>spread***) to DTS,
>and process the resultant "rows"?
>
>I looked on both sqlxml.org and sqldts.com and didn't see any relevant
>examples.
>
>Thanks for any help you can provide!
>
>John Peterson
>
>
You cannot do this out of the box with DTS because it does not contain a
OLE-DB to XML driver. You can get them from third party suppliers.

The way I do it is to use the SQL XML Bulk Load object. This is
available as the SQLXML 3 download, free from MS. There is a sample on
sqlxml.org that demonstrates how to call this from an ActiveX Script
Task. 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org
Quantcast