SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hello SSIS World, From XML!


Hello SSIS World, From XML!

Author
Message
Phil Brammer
Phil Brammer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 640
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
peter.howard
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 57
Why did you use an ADO .NET Source instead of a OLE DB SOurce?
Stan Kulp-439977
Stan Kulp-439977
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 1125
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
Phil Brammer
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 640
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. Wink

Your solution works great if you want to do something on a row-by-row basis - like call a Web service.
Diana Hayn-Williams
Diana Hayn-Williams
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 39
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
Stan Kulp-439977
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 1125
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" ?>
<!--Created with Liquid XML Studio - FREE Community Edition 7.0.2.746 (http://www.liquid-technologies.com)-->
<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
Stan Kulp-439977
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 1125
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search