SSIS Web Service To XML Source To SQL Server Destination Problem

Hi,



I have an SSIS package which calls a web service and returns a Dataset object in the form of an xml file (it does this successfully - and stores it successfully).

The Data Flow then picks up the file using an xml source task (in which use inline schema is specified) , and passes it to an SQL Server Destination task - for bulk load.


I've checked the mappings and everything seems to be ok, the package gives no warnings or errors when run.

The destination table is created on the SQL Server without problem, but at that stage the task finishes "successfully", without actually loading any table rows into the destination table.

Also in the SQL Server Destination task - when I select "preview" in the connection manager section - I can see the column definitions, but again - no rows of data.


Thanks to Simon Sabin for pointing me here - I did post on the managed msdn newsgroups in sql server programming but have had no replies as yet.

Below is the beginning of the xml Dataset object which is output as an xml file for information. If you need any more information just let me know.

Not quite sure where i'm going wrong here - thanks in advance



Crispin.








Code Snippet

<?xml version="1.0" encoding="utf-16"?>
<DataSet>
<xs:schema id="callStatisticsDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchemahttp://www.w3.org/2001/XMLSchema">http://www.w3.org/2001/XMLSchema</A< A>>" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="callStatisticsDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType>
<xs:sequence>
<xs:element name="date" type="xs:dateTime" minOccurs="0" />
<xs:element name="callID" type="xs:int" minOccurs="0" />
<xs:element name="cli" type="xs:string" minOccurs="0" />
<xs:element name="itemClosed" type="xs:string" minOccurs="0" />
<xs:element name="custProdId" type="xs:string" minOccurs="0" />
<xs:element name="itemSaleId" type="xs:string" minOccurs="0" />
<xs:element name="phoneNumber" type="xs:string" minOccurs="0" />
<xs:element name="lastXml" type="xs:string" minOccurs="0" />
<xs:element name="followedPath" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<callStatisticsDataSet>
<Table diffgr:id="Table1" msdata:rowOrder="0">
<date>2007-06-04T08:03:10.02+01:00</date>
<callID>85560695</callID>
<cli>648477292</cli>
<itemClosed />
<custProdId>-1</custProdId>
<itemSaleId />
<phoneNumber />
<lastXml />
<followedPath>Initialize #waitList;|tvShop.CallInitiated;|Is not closed?;|No item on sale;|Store Items;</followedPath>
</Table>