Hello SSIS World, From XML!

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Phil Brammer Ten Centuries Points: 1135 More actions · Answer 6

Why was this not done entirely in the data flow, where batch inserts could be done, as opposed to row-by-row in your method? You can use a data viewer in the data flow to inspect the data as well, rather than going through all of the extra work to display a message box?

peter.howard SSC Journeyman Points: 95 More actions · Answer 7

Why did you use an ADO .NET Source instead of a OLE DB SOurce?

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 8

I just meant this as an introduction, thus the "Hello World" allusion.

The hardest part is just figuring out some way to get XML into the project.

As you point out, once you get the XML source working, there are a million ways to go from there.

Phil Brammer Ten Centuries Points: 1135 More actions · Answer 9

Stan Kulp-439977 (4/15/2010)
I just meant this as an introduction, thus the "Hello World" allusion.

Agreed, but using a recordset/foreach loop and a script task is not quite "hello world" material. Connecting the source you already have in your data flow to an OLE DB Destination and right-clicking on the path between them and selecting "Data Viewer" is, however. 😉

Your solution works great if you want to do something on a row-by-row basis - like call a Web service.

Diana Hayn-Williams SSC Journeyman Points: 79 More actions · Answer 10

I found the .dtxs package but I don't know what version was used here. When I open up the XML Source component, there is NO "Generate XSD" button.???? I tried in both SSIS 2005 and 2008 and neither one has a Generate XSD button. And I'm using the Developer Edition..

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 11

I made it in 2005. I just got 2008 Developer and it's not there. In 2005 the XSD file says it was created by Liquid XML Studio Community Version. Since Liquid discontinued the Community version, I guess they had to ditch the functionality.

You can use the free version of XMLFox (http://www.xmlfox.com/) to generate one.

Or you can copy-and-paste this code I just generated:

<?xml version="1.0" encoding="utf-8" ?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="ROOT">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="unbounded" name="row">

<xs:complexType>

<xs:attribute name="firstName" type="xs:string" use="optional" />

<xs:attribute name="lastName" type="xs:string" use="optional" />

<xs:attribute name="city" type="xs:string" use="optional" />

<xs:attribute name="state" type="xs:string" use="optional" />

<xs:attribute name="phone" type="xs:string" use="optional" />

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 12

I know it's more complicated than a Hello World. I had it simpler the first version, but the editor wanted more.

To me the important part is just getting the XML Source to work. Once you know how to do that, the rest is just stuff you've already been doing in SSIS.

It took me a while to even figure out that the XML Source existed.