Call a Web Service using SQL Server Integration Services then Store and Query the Results

Introduction

Often enough, there is a huge benefit in knowing how to leverage SQL Server Integration Services to accomplish a data task that depends on a remote data source. This is a tutorial about a seemingly simple problem and solution that involves calling a web service from a SQL Server Integration Services package, storing the XML in an XML-typed column in a SQL Server database table, then extracting the data using XQuery. Note that web services may also return JSON, which I’ll explain and write a tutorial about later. This simple process and solution can be repeated to gather information from virtually any public web service. Use this method with care and purpose.

Continue reading “Call a Web Service using SQL Server Integration Services then Store and Query the Results”

Solving SQL Server Integration Services Expressions

Solving SQL Server Integration Services Expressions using Expression Builder is a bit of a chore until you have a good understanding and a few working examples of some useful expressions. Here are several common exceptions you might see when working within Expression Builder as well as examples and explanations of correct syntax.

The exception:

Cannot convert System.Int32 to System.String

Here are a couple more exceptions you may come across:

“Cannot convert expression value to property type.”
“Cannot convert ‘System.DateTime’ to ‘System.String’.

Continue reading “Solving SQL Server Integration Services Expressions”