Simons SQL Blog : Tips and Tricks, SQL Server 2008, SSIS - Integration Services

SSIS - XML Source Script

simonsabin — Thu, 11 Mar 2010 20:20:55 GMT

The XML Source in SSIS is great if you have a 1 to 1 mapping between entity and table. You can do more complex mapping but it becomes very messy and won't perform. What other options do you have?

The challenge with XML processing is to not need a huge amount of memory. I remember using the early versions of Biztalk with loaded the whole document into memory to map from one document type to another. This was fine for small documents but was an absolute killer for large documents.

You therefore need a streaming approach.

For flexibility however you want to be able to generate your rows easily, and if you've ever used the XmlReader you will know its ugly code to write.

That brings me on to LINQ. The is an implementation of LINQ over XML which is really nice. You can write nice LINQ queries instead of the XMLReader stuff. The downside is that by default LINQ to XML requires a whole XML document to work with. No streaming.

Your code would look like this. We create an XDocument and then enumerate over a set of annoymous types we generate from our LINQ statement

XDocument x = XDocument.Load("C:\\TEMP\\CustomerOrders-Attribute.xml");

foreach (var xdata in (from customer in x.Elements("OrderInterface").Elements("Customer")

from order in customer.Elements("Orders").Elements("Order")

select new { Account = customer.Attribute("AccountNumber").Value

, OrderDate = order.Attribute("OrderDate").Value }

))

{

Output0Buffer.AddRow();

Output0Buffer.AccountNumber = xdata.Account;

Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

}

As I said the downside to this is that you are loading the whole document into memory.

I did some googling and came across some helpful videos from a nice UK DPE Mike Taulty http://www.microsoft.com/uk/msdn/screencasts/screencast/289/LINQ-to-XML-Streaming-In-Large-Documents.aspx. Which show you how you can combine LINQ and the XmlReader to get a semi streaming approach. I took what he did and implemented it in SSIS. What I found odd was that when I ran it I got different numbers between using the streamed and non streamed versions. I found the cause was a little bug in Mikes code that causes the pointer in the XmlReader to progress past the start of the element and thus

foreach (var xdata in (from customer in StreamReader("C:\\TEMP\\CustomerOrders-Attribute.xml","Customer")

from order in customer.Elements("Orders").Elements("Order")

select new { Account = customer.Attribute("AccountNumber").Value

, OrderDate = order.Attribute("OrderDate").Value }

))

{

Output0Buffer.AddRow();

Output0Buffer.AccountNumber = xdata.Account;

Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

}

These look very similiar and they are the key element is the method we are calling, StreamReader. This method is what gives us streaming, what it does is return a IEnumerable list of elements, because of the way that LINQ works this results in the data being streamed in, it returns the elements one at a tiem rather than building a collection of them. The key is the use of the IEnumerable and the "yield return"

static IEnumerable<XElement> StreamReader(String filename, string elementName)

{

using (XmlReader xr = XmlReader.Create(filename))

{

xr.MoveToContent();

while (xr.Read()) //Reads the first element

{

while (xr.NodeType == XmlNodeType.Element && xr.Name == elementName)

{

XElement node = (XElement)XElement.ReadFrom(xr);

yield return node;

}

xr.Close();

}

This code is specifically designed to return a list of the elements with a specific name. The first Read reads the root element and then the inner while loop checks to see if the current element is the type we want. If not we do the xr.Read() again until we find the element type we want. We then use the neat function XElement.ReadFrom to read an element and all its sub elements into an XElement. This is what is returned and can be consumed by the LINQ statement. Essentially once one element has been read we need to check if we are still on the same element type and name (the inner loop) This was Mikes mistake, if we called .Read again we would advance the XmlReader beyond the start of the Element and so the ReadFrom method wouldn't work.

To get this working,

1. Put a script component in your data flow as a source component.

2. Then add the columns you wish to the output.

3. Add the StreamReader function to your script code

4. Put the first foreach in the CreateNewOutputRows method

4. Change the LINQ query etc to match what you want

With the code above you can use what ever LINQ statement you like to flatten your XML into the rowsets you want. You could even have multiple outputs and generate your own surrogate keys.

XML Validation in SQL Server Intergration Services

simonsabin — Wed, 20 Jan 2010 23:30:58 GMT

I've been workig with XML in SQL Server Integration services this week and to say the XML task in SQL Server Integration services sucks is an understatement. Its buggy, the documentation is woefully inadequate in some cases wrong. I believe this was one of the first components written back for SQL 2005 so you can imagine it was written along time ago, XML wasn't as standardised.

So what if you want to validate an XML document against a XSD Schema. Well in true SQL Server Integration services mode the answer is to do it yourself with a script task.

There are two ways of doing it, the first uses an XmlDocument object and the other is the XmlReader. If you were on my XML course at SQLBits you will remember that I pointed out the importance of straeming when dealing with large data and this is where these two differ. The XmlDocument loads the document into memory, whereas the XmlReader is a streaming interface to the XML document you have. So if you have a large document you should use the XmlReader.

In my case however I wanted to pass the XML to a stored procedure after it had been validated and so I used the XmlDocument.

The first step is to create a XmlSchemaSet and put your schema into it.

string Filepath = Dts.Variables["User::FullNewFilename"].Value.ToString();

string Schema= Dts.Variables["User::SchemaFilepath"].Value.ToString();

// Create a schema set

XmlSchemaSet schemaset = new XmlSchemaSet();

// Add the schema to the collection.

schemaset.Add(XmlSchema.Read(XmlReader.Create (Schema),null));

Here Schema is a variable to the path for the schema and Filepath is the variable to the Xml file to be validated.

After that you have to either use the XmlDocument or XmlReader. For the XmlDocument we are passing back the contents of the file using the InnerXml property.

//clear our errors string builder

ValidationErrors = new StringBuilder();

//Use this if you need to read the document for use elsewhere

XmlDocument doc = new XmlDocument();

doc.Schemas = schemaset ;

doc.Load(Filepath );

doc.Validate(new ValidationEventHandler(ValidationCallBack));

//Load the contents of file into the variable for later processing.

Dts.Variables["ImportFileContents"].Value = doc.InnerXml;//clear our errors string builder

or use the XmlReader, this requires another object the XmlReaderSettings object

//set the validation settings.

XmlReaderSettings settings = new XmlReaderSettings();

settings.ValidationType = ValidationType.Schema;

settings.Schemas = schemaset ;

settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);

using (XmlReader xr = XmlReader.Create(Filepath,settings ))

{

//This reads the whole document through the reader and does the validation

while (xr.Read()) ;

//Close the reader

xr.Close();

}

In both you will see that they have references to the ValidationCallBack. The validation model is a callback model and so you need this extra function. This is why we are using a global variable ValidationErrors to store the errors and then be able to access them after the validation succeeds. The validation callback function is described here

// Display any validation errors.

private void ValidationCallBack(object sender, ValidationEventArgs e)

{

ErrorOccurred = ErrorOccurred || e.Severity == XmlSeverityType.Error;

Warning= Warning|| e.Severity == XmlSeverityType.Error;

if (ValidationErrors.Length > 0)

{

ValidationErrors.AppendLine();

}

ValidationErrors.Append(e.Message);

}

Complete Code

So putting it all together we get

StringBuilder ValidationErrors;

bool ErrorOccurred= false;

bool Warning = false;

public void Main()

{

string Filepath = Dts.Variables["User::FullNewFilename"].Value.ToString();

string Schema= Dts.Variables["User::SchemaFilepath"].Value.ToString();

// Create a schema set

XmlSchemaSet schemaset = new XmlSchemaSet();

// Add the schema to the collection.

schemaset.Add(XmlSchema.Read(XmlReader.Create (Schema),null));

//clear our errors string builder

ValidationErrors = new StringBuilder();

//Use this if you need to read the document for use elsewhere

XmlDocument doc = new XmlDocument();

doc.Schemas = schemaset ;

doc.Load(Filepath );

doc.Validate(new ValidationEventHandler(ValidationCallBack));

//Load the contents of file into the variable for later processing.

Dts.Variables["ImportFileContents"].Value = doc.InnerXml;

/* //Otherwise use the following

// Set the validation settings.

XmlReaderSettings settings = new XmlReaderSettings();

settings.ValidationType = ValidationType.Schema;

settings.Schemas = schemaset ;

settings.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);

using (XmlReader xr = XmlReader.Create(Filepath,settings ))

{

//This reads the whole document through the reader and does the validation

while (xr.Read()) ;

//Close the reader

xr.Close();

}*/

if (ValidationErrors.Length > 0)

Dts.Variables["XMLError"].Value = ValidationErrors.ToString();

Dts.Variables["Validation"].Value = ErrorOccurred?"Error":(Warning?"Warning":"");

}

// Display any validation errors.

private void ValidationCallBack(object sender, ValidationEventArgs e)

{

ErrorOccurred = ErrorOccurred || e.Severity == XmlSeverityType.Error;

Warning= Warning|| e.Severity == XmlSeverityType.Error;

if (ValidationErrors.Length > 0)

{

ValidationErrors.AppendLine();

}

ValidationErrors.Append(e.Message);

}

Enjoy.

SQL Server 2008 - Killer features - MERGE and Table Valued Parameters

simonsabin — Tue, 17 Jun 2008 07:30:16 GMT

Table valued parameters along with MERGE are in my view the two killer features and the two that I believe will get most uptake when people start to use SQL 2008.

Table valued parameters enable you to send a set of data as a single parameter to your code whether it be adhoc SQL, a stored procedure or function. This is beneficial as it reduces the number of calls that have to be made to the database and so you minimise the overhead associated with each call. Imagine saving 1000 items into a table the historical way was to either, issue 1000 database calls to do the inserts or construct a single batch that consisted of all the insert statements. (This was the behaviour with ADO). Even with the latter approach where you only have 1 round trip to the database you still have the overhead associated with a single insert. I compare it to photo copying a set of pages, you can either copy each page one at a time, by lifting the lid on the copier, putting the paper on the platen and then copying and repeating for each sheet or the quicker way is to put them all in the sheet feeder and let the machine process the whole set of pages in one go.

Table valued parameters really come into their own when you have a distributed application and the speed of the link between application and database isn’t very fast. In the scenario above if the latency in the network results in a call to the db taking 10 ms (irrespective of what you are doing), then it will take 10s to save the data. Converting to a table valued parameter would reduce this down to 1 x 10ms overhead for the database call.

The MERGE statement further enhances table valued parameters by enabling you to pass a single set of data consisting of inserts, updates and deletes and save those to the database in a single statement. This has the benefit in that it simplifies code, you have a single atomic transaction and so the complexities of serialized transactions may be avoided, you get performance benefits from the fact that the index structures are being modified in a single set based approach rather than 3 individual statements performing inserts, updates and deletes. (In one test I did I found using MERGE to do purely INSERTs was quicker than the equivalent insert statement, so it is by no means a compromise).

Using AdventureWorks we have the following demo.

Firstly we need to create a TABLE TYPE for use by our Table Valued Parameter. In this case we have copied the structure of the SalesOrderDetail type.

CREATE TYPE SalesOrderDetail_Type as TABLE (

[SalesOrderID] [int] NOT NULL ,

[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[OrderQty] [smallint] NOT NULL,

[ProductID] [int] NOT NULL,

[SpecialOfferID] [int] NOT NULL,

[UnitPrice] [money] NOT NULL,

[UnitPriceDiscount] [money] NOT NULL,

[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,

[ModifiedDate] [datetime] NOT NULL, PRIMARY KEY (SalesOrderId,SalesOrderDetailID))

Secondly we create our procedure that uses the Table Type. We can then call this procedure from TSQL or client code passing in a set of data.

CREATE PROCEDURE up_OrderDetail_SaveSet

@ChangedOrderDetail SalesOrderDetail_Type READONLY

,@SalesOrderID int

MERGE Sales.SalesOrderDetail OrderDetail

USING @ChangedOrderDetail Chng ON Chng.[SalesOrderDetailID] = OrderDetail.[SalesOrderDetailID]

AND Chng.[SalesOrderID] = OrderDetail.[SalesOrderID]

WHEN MATCHED THEN

UPDATE SET [SalesOrderID] = Chng.SalesOrderID

,[CarrierTrackingNumber] = Chng.CarrierTrackingNumber

,[OrderQty] = Chng.OrderQty

,[ProductID] = Chng.ProductID

,[SpecialOfferID] = Chng.SpecialOfferID

,[UnitPrice] = Chng.UnitPrice

,[UnitPriceDiscount] = Chng.UnitPriceDiscount

,[rowguid] = Chng.rowguid

,[ModifiedDate] = Chng.ModifiedDate

WHEN NOT MATCHED THEN

INSERT ([SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID]

,[UnitPrice],[UnitPriceDiscount],[rowguid],[ModifiedDate])

VALUES

(Chng.SalesOrderID,Chng.CarrierTrackingNumber,Chng.OrderQty,Chng.ProductID,Chng.SpecialOfferID

,Chng.UnitPrice,Chng.UnitPriceDiscount,Chng.rowguid,Chng.ModifiedDate);

SSIS : Web Service task doesn't cut it but who cares

simonsabin — Mon, 02 Jul 2007 20:36:38 GMT

18 months ago I was trying to use the SSIS web service task for a number of purposes. The first was to integrate with SalesForce, the second was to manage reporting services. Unfortunately the Integration Services (SSIS) web service task doesn't work for these web services. On further investigation I'm not sure what web services it does support.

This was discussed in the MSDN forums http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161653&SiteID=1 and subsequently raised in connect https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126247

Well it's not going to be fixed in Katmai. You might wonder why I'm not ranting about this not being fixed and how crap Microsoft are. Well I'm not because of another enhancement in SSIS. I blogged about an associated change a few weeks ago (SSIS - Sweet sight - Visual Studio 2005 Tools For Applications). What this gives is the ability to add references to your Script task, including web services. That makes it very easy to use web services now in SSIS.