You are currently browsing the archive for the .NET category.

A Record SetAs you may already know, it is possible to use the Execute SQL Task to populate a package variable with a result set.

In case you’re not that familiar with this technique yet, here are a quick two words on setting that up.  You just give it a query, set the ResultSet property to Full result set and configure a package variable in the Result Set property window.  The package variable’s type is System.Object.

But what exactly is this mysterious System.Object and how can we explore it?  Well, that depends.  More precisely, it depends on the Connection Type which you’ve chosen in the Execute SQL Task properties.

Let’s explore two possibilities: ADO.NET and OLE DB.  Our end goal is straightforward: retrieve the number of records in the result set.

The query which I’m using in the Execute SQL task is this one:

select ProductAlternateKey
from dbo.DimProduct
where Color = 'blue'

On my AdventureWorksDW2012 database it should return 28 records: 28

Exploring the ADO.NET result set

The first step is finding out what type exactly this result set object is.  Hook up a Script Task to your Execute SQL task and put a breakpoint on it.  Now run your package and examine the Locals window:

Debugging the Control Flow to find the object type

Well look at that, it’s a System.Data.DataSet!  Using this knowledge it’s fairly simple to produce code that fetches the record count:

DataSet ds = (DataSet)Dts.Variables["MyResultset"].Value;

Note: don’t forget to add the package variable to the ReadOnlyVariables before opening the code editor.

The System.Data namespace is included by default in the using statements, no worries there.  So we can just cast the variable into a Dataset.  The DataSet object contains a DataTableCollection called Tables.  As there’s only one result set this is located at index zero.  We travel down the object tree to finally find the Count property of the Rows DataRowCollection.

And here’s the result:

The message box shows 28 items

That’s all there’s to it, easy huh?  Let’s move on to our second option, OLE DB.

Exploring the OLE DB result set

Once again we start at the beginning: with the debugging of the Control Flow to find out what object type our mysterious System.Object is:

The OLE DB result set gives us a System.__ComObject, hmm...

Hmm, System.__ComObject, that’s … special.  Ow right, the OLE DB provider uses a COM wrapper.  How can we “unwrap” our object and introduce it to the .NET world?  Let’s see if we can find out what’s hidden behind that wrapper, by using the following code:


TypeName is a VB.NET function and retrieves the data type of the parameter passed into it.

To get this to run in a C# SSIS task you first need to add the Microsoft.VisualBasic reference:

Adding a reference to the VB.NET assembly

Executing the package results in this:

Result type: Recordset

So, our result is Recordset, hmm, well, I think we more or less knew this already.  What kind of Recordset?  Well, an ADO Recordset.  We know this because the following code works:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["MyResultset"].Value);

Basically, we use the Fill method of the OleDbDataAdapter to fill a System.Data.DataTable with the data from the ADO Recordset.  The version of the method in our example (there are several overrides) accepts two parameters:

public int Fill(

DataTable dataTable,

Object ADODBRecordSet


With the DataTable filled we’ve got once again access to a Rows DataRowsCollection, exactly the same as in our ADO.NET example in fact.  Executing the package now results in exactly the same message box as shown earlier: 28 records!

Beware of pitfalls

If you mix the two methods up you’ll get funky errors such as:

System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

and also

System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.

So be careful, use the right object types for your particular System.Object.


In this article I’ve demonstrated a couple of methods which can be used to retrieve information from the mysterious System.Object result set in an SSIS package.

Have fun!


Additional References

Execute SQL Task

Result Sets in the Execute SQL Task

OleDbDataAdapter Class


Tags: , ,

About a week ago Gill announced that he’d finished and published the new website so here’s my part of spreading the word.


Click the banner above to register for the free event or put your browser on www.communityday.be.



This may not be your daily routine but in today’s world of interoperability and loosely-coupled systems you sometimes don’t have another option.  Imagine a situation where a scheduled task (using SQL Agent) needs to get data from a web service or trigger some code through a web service.

In the following explanation I will show how to use SQL CLR (Common Language Runtime) Integration to get a weather report from a web service, or WS in short.

For this I am using a free web service: http://www.webservicex.net/globalweather.asmx.  Or not…  Seems that the weather web service gods don’t like me, this WS has (temporarily?) stopped working after I completed the sample and part of this article.  I haven’t found a replacement that can deliver global weather so we’ll have to do with US-only, delivered through this web service: http://ws.cdyne.com/WeatherWS/Weather.asmx.

Now is a good time to warn you about the usage of external services.  If you need your code to work all the time, make sure that you can trust the web services that you use.  Otherwise you may just end up as I did, with a dead function…

Basic knowledge on building .NET assemblies and using SQL Server is required.

The assembly

Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2005, create a new Database Project:


Add a Web Reference to your project pointing to the .asmx mentioned earlier.


Add a Stored Procedure to your project:


This class is going to contain the code to call the web service and return a result to the client.

Following code fetches the weather report for a given US Zip code.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MyStoredProcedures.com.cdyne.ws;

public partial class StoredProcedures
    public static void WeatherSP(string zipCode)
        Weather myWeatherWS = new Weather();
        WeatherReturn weatherResult = myWeatherWS.GetCityWeatherByZIP(zipCode);

        SqlMetaData[] recordMetaData = new SqlMetaData[2];
        // layout of the records that we'll return
        recordMetaData[0] = new SqlMetaData("Description", SqlDbType.Char, 100);
        recordMetaData[1] = new SqlMetaData("Value", SqlDbType.Char, 1000);

        // build a record based on the metadata
        SqlDataRecord record = new SqlDataRecord(recordMetaData);

        // let's start sending result into the active pipe

        // one way of adding a new record to the pipe
        record.SetString(0, "ResponseText");
        record.SetString(1, weatherResult.ResponseText);

        // another way of adding a new record, using an array initializer
        record.SetValues(new String[2] { "City", weatherResult.City });

        record.SetValues(new String[2] { "State", weatherResult.State });

        record.SetValues(new String[2] { "Description", weatherResult.Description });

        record.SetValues(new String[2] { "Temperature", weatherResult.Temperature });

        record.SetValues(new String[2] { "Timestamp", DateTime.Now.ToString() });

        // finished sending result


In the code above you can see that the stored procedure has a parameter called zipCode.  This incoming value is passed to the web service call.

Our result consists of several records, each record containing 2 string fields.

The web service that we’ve used returns an object of type WeatherResult.  This makes it easy for us to read the result.  The WS that I used initially didn’t return an object but an XML string.  In that case you can use the XmlDocument.LoadXml from the System.Xml namespace to get an XmlDocument.  Then use the SelectSingleNode method to get to the details, for instance myXmlDoc.SelectSingleNode(“//Temperature”).InnerText would return the value for the Temperature node.

And this is what it looks like when our stored procedure gets called from the Management Studio:


Building the assembly

Before we build our assembly there are a couple of project settings that need modification.

Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.


Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence external.


Server and Database Settings

Now that we’ve covered the actual development of the stored procedure we would like to install it on our database server.  This requires us to modify some settings on the server and database.  If you are not the DBA of the server and you are in the luxurious situation that such a person exists, please verify with him/her if you are allowed to modify these settings.

Enabling CLR Integration

By default SQL Server does not allow CLR Integration.  This setting can be easily modified with the following script:

exec sp_configure 'clr enabled', '1';

Our database we trust

As our stored procedure needs external access permission, we need to create the assembly with external access (as shown in next chapter).  To get this to work we need to convince SQL Server that our database can be trusted: (I’ve used AdventureWorks in this article but this can be any existing database)


Owner needs external access

To be allowed to create an assembly with external access, the database owner needs to have EXTERNAL ACCESS ASSEMBLY permission.  If that’s not the case you will see the following error when trying to create the assembly:
Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly ‘MyStoredProcedures’ failed because assembly ‘MyStoredProcedures’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
So to avoid this error make sure that your DB owner has sufficient permissions.  The following script makes sa the owner of the active database:
EXEC sp_changedbowner 'sa', false;

Installing the Stored Procedure

We’re almost there, all that remains to be done is to tell SQL Server where our stored procedure can be found and that it actually exists.  Assemblies, just like stored procedures, live in a database (AdventureWorks in our case).  In Management Studio you can see your assemblies in the Object explorer under the Programmability node of your database:


Adding the assemblies

Following script adds the two assemblies that we’ve created earlier to your database.  An assembly is given a name, like MyStoredProcedures.  Here you can finally see the EXTERNAL_ACCESS permission that we need for our web service call.

CREATE ASSEMBLY MyStoredProcedures
FROM 'D:\vvr\techtests\dotnet\MyStoredProcedures\MyStoredProcedures\bin\Debug\MyStoredProcedures.dll'


Adding our stored procedure

Last step is to create our stored procedure.  We tell SQL Server that it can be found in the MyStoredProcedures assembly, in the class StoredProcedures and that the method is called WeatherSP.  We have one parameter of type string in our method definition, which translates to nvarchar in the stored procedure.  A length of 10 is more than enough for our ZIP code.

    @zipCode nvarchar(10)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

Configuring the Stored Procedure

In some (most?) cases we don’t want to hard-code connection details. In this case it means the path to the web service. Imagine a situation where you are consuming a web service that you’ve developed yourself, or another team in your company. And the web service is running in several environments: development, acceptance, production. Of course we don’t want to change our code and recompile each time we need to deploy to a different environment. So we need a way to configure the web service location.

The answer here lies in the sqlservr.exe.config file. This is a file like any other .config file, with a possible content like this:

<?xml version="1.0" encoding="utf-8" ?>
    <add key="MyWebService" value="http://MyWebServiceServer:MyWSPort/PathTo/MyWebService.asmx" />

This file needs to be put in the same folder as your sqlservr.exe that’s running your SQL Server instance.  By default this path is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\.  When you restart your SQL Server instance while this file is in that folder, SQL Server will pick up any configuration settings from that file.  These can then be accessed from your custom stored procedure in this way:

/* The following call is made in order to workaround an "anomaly"
   * where reading an appSettings key value results in the error:
   * "The value of the property 'key' cannot be parsed.
   * The error is: Request failed. (sqlservr.exe.Config line X)"
   * So, just a call to another section alleviates the issue.
   * This error only occurs on the first call to an assembly after
   * it's been loaded by the sqlservr.exe
int firstcall = ConfigurationManager.ConnectionStrings.Count;

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];

That’s it, that’s all it takes.  It’s not that difficult, once you know it, right? :-)

Have fun!



Tags: , , , , , , ,

The other day I was looking for an alternative Reporting Services client, as an extra client besides the web-based Report Manager. As I couldn’t really find one I thought “how difficult could it be to write one myself?”.

And indeed, with the ReportViewer control in Visual Studio 2005 it’s really no big deal. All you need to do is drag the control onto a form, tell it that you want to load a remote report, configure the ReportServer so that it knows where to look for reports and the ReportPath so that it knows what report to load. Then call the RefreshReport() method to load the report.

When configuring the ReportPath, pay attention to the mandatory leading slash.

   1: reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
   2: reportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver");
   3: reportViewer1.ServerReport.ReportPath = "/My Reports/ReportName";
   4: // load report
   5: reportViewer1.RefreshReport();

The C# code snippet above is loading a report called ReportName, located on the local report server in a folder called My Reports.

Update: how to browse the reports on the server

Microsoft has provided an interesting sample called RSExplorer as part of the SQL Server samples, located at CodePlex.  Once you’ve installed the samples you can find RSExplorer in C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Application Samples\RSExplorer Sample\, if you chose to install to the default folder.

The sample shows how to use the Reporting Services web service to populate a WinForms ListView with a list of folders and reports, and even how to do some basic management tasks like creating a folder or editing a report description.

When you double-click a report it will open in a popup window.  What I do find funny in this sample is that the popup window, called ReportViewer, does not use the ReportViewer control.  It uses a WebBrowser control  instead.


Tags: , , , ,

If you’re doing some Excel automation like creating a sheet with some graphs from .NET, you may run into the “Old format or invalid type library” error which is quite nicely described in this Microsoft Support article.  At this moment there’s no fix available, but the article describes not one, not two, but three workarounds.  I chose the third one where you switch the Culture for your thread to “en-US”.

When giving this a try, it solved the error mentioned above but then I started getting “Exception from HRESULT: 0x800A03EC” errors.  After searching for a while I finally found the reason: you should not switch the Culture back to the original one after you’ve added your new Excel workbook as shown in the workaround.  First finish whatever you want to automate in Excel and switch the Culture back at the end.

In C# this looks like the following:

   1: // code below assumes the following using statement
   2: // using Excel = Microsoft.Office.Interop.Excel;
   4: // capture current Culture settings
   5: System.Globalization.CultureInfo systemCultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
   6: try
   7: {
   8:     // temporarily change CultureInfo to en-US
   9:     System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
  11:     Excel.Application excelApplication = new Excel.Application();
  12:     // create new workbook
  13:     Excel._Workbook workbook = (Excel._Workbook)(excelApplication.Workbooks.Add(Missing.Value));
  14:     // get active sheet
  15:     Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet;
  17:     // create your graphs or whatever you were planning to automate in Excel
  19:     // give the user control over Excel
  20:     excelApplication.Visible = true;
  21:     excelApplication.UserControl = true;
  22: }
  23: catch (Exception ex)
  24: {
  25:     // handle exception
  26: }
  27: finally
  28: {
  29:     // put CultureInfo back to original
  30:     System.Threading.Thread.CurrentThread.CurrentCulture = systemCultureInfo;
  31: }

Tags: , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved