URGENT - Using XML Source - Related issues and usage

Using XML Source - Related issues and usage

Posted 10th July 2008

** URGENT Help Required **

Tools using:-
SQL Server 2005
SSIS (devenv.exe)

Already tried to look for articles on the net and consulted Help inside SSIS
(both local and web).

Project trying to do....
I have been provided access to a secure web site using a specific URL. The
URL contains switches to control the data being shown.
Essentially I have been given access to a web report.
Once the URL is executed, secure details entered, a web page then appears
with XML data on it.
The job is to get this XML data directly in to a SQL Server database table
using SSIS.

What I have tried....

TRY HTTP - Tried using HTTP Connection Manager to connect to the web report
* New Solution Project.
* HTTP Connection Manager object.
- Server page.
> Server URL of
https://part1.companyname.com/reportbi.php?switch1=switchdata&switch2=...tchdata
> Secure credentials entered in the appropriate places.
- Proxy page.
> Appropriate settings were placed here.
- Server page again.
- Clicked on Test Connection button.
> **** Unable to connection to the remote server. (this happens if I
use proxy or no proxy) ****

TRY XML - After saving the XML page to disk I attempted to try and read it
using SSIS XML Source object....
* New Solution Project.
* Data Flow Task.
* XML Source Object.
- Connection Manager page.
> XML File Location.
> Pointed to XML file I have.
> Pointed to XSD file I have (generated XSD file using MS BizTalk
functionality).
- Columns page.
> **** No columns are available when there should be ****
- Error Output page.
> Page is blank.
- Clicked on OK button to accept changes
> **** Error 5 Validation error. Data Flow Task: XML Source 1 [467]: A
component does not have any inputs or outputs.
Package.dtsx 0 0 ****

What help I am asking for....
Q1) Given the project brief in the "Project trying to do" section above what
is the ideal route in SSIS to achieve this.
Q2) If I have got Q1 correct above using HTTP Connection, Data Flow (XML
Source -> SQL Server Database) how do I resolve the issues
I am getting above?
Q3) Are there any examples on the web that do what I am trying to do above?

Any help is appreciated.

NOTE: Below is a copy of the XML file and the XSD file.

Many thanks in advance,
Regards,
Richard Sterling.

***** XML File BEGIN ******
<?xml version="1.0" encoding="utf-8"?>
<report name="MyReport01">
<row id="1">
<column name="StatisticsDate">2008-07-03</column>
<column name="Username">TESTUSER01</column>
<column name="AdvertiserProfileID">82939</column>
<column name="AdvertiserUsername">mydefault</column>
<column name="TotalBets">8.2</column>
<column name="TotalWins">9.3</column>
<column name="Currency">GBP</column>
<column name="RedeemedCasinoBonuses">0</column>
<column name="ConvertedComps">0</column>
<column name="CompPointsBalance">450.54</column>
<column name="Casino">mycasino</column>
<column name="Rake">0</column>
<column name="TournamentFees">0</column>
<column name="PokerBonusesRedeemed">0</column>
<column name="iPointsRedeemed">0</column>
<column name="AmountOfTokensUsed">0</column>
<column name="cust_id">1234</column>
<column name="GamesCount">23</column>
</row>
<row id="2">
<column name="StatisticsDate">2008-07-03</column>
<column name="Username">TESTUSER02</column>
<column name="AdvertiserProfileID">123</column>
<column name="AdvertiserUsername">default02</column>
<column name="TotalBets">10</column>
<column name="TotalWins">0</column>
<column name="Currency">GBP</column>
<column name="RedeemedCasinoBonuses">10</column>
<column name="ConvertedComps">0</column>
<column name="CompPointsBalance">2466</column>
<column name="Casino">mycasino2</column>
<column name="Rake">0</column>
<column name="TournamentFees">0</column>
<column name="PokerBonusesRedeemed">0</column>
<column name="iPointsRedeemed">0</column>
<column name="AmountOfTokensUsed">0</column>
<column name="cust_id">2445</column>
<column name="GamesCount">1</column>
</row>
</report>
***** XML File END ******

***** XSD File BEGIN ******
<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003"
xmlns="http://myserver.Schema1" targetNamespace="http://myserver.Schema1"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="MyReport01">
<xs:complexType>
<xs:sequence>
<xs:element name="StatisticsDate" type="xs:string" />
<xs:element name="Username" type="xs:string" />
<xs:element name="AdvertiserProfileID" type="xs:string" />
<xs:element name="AdvertiserUsername" type="xs:string" />
<xs:element name="TotalBets" type="xs:string" />
<xs:element name="TotalWins" type="xs:string" />
<xs:element name="Currency" type="xs:string" />
<xs:element name="RedeemedCasinoBonuses" type="xs:string" />
<xs:element name="ConvertedComps" type="xs:string" />
<xs:element name="CompPointsBalance" type="xs:string" />
<xs:element name="Casino" type="xs:string" />
<xs:element name="Rake" type="xs:string" />
<xs:element name="TournamentFees" type="xs:string" />
<xs:element name="PokerBonusesRedeemed" type="xs:string" />
<xs:element name="iPointsRedeemed" type="xs:string" />
<xs:element name="AmountOfTokensUsed" type="xs:string" />
<xs:element name="cust_id" type="xs:string" />
<xs:element name="GamesCount" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
***** XSD File END ******