How to Call a .NET Assembly From a SQL Server Scheduled Job

This is a step-by-step example of an unmanaged call, i.e. from a DTS Active X script, to a .NET assembly, using a SQL Server scheduled Job as the caller's context. Almost any class in .NET can be exposed to a COM client simply by exporting the information for its assembly and registering the assembly with COM. The class must have a default constructor, and the methods, properties, fields, and events you want to expose must be public.

I'll now show you a step-by-step implementation of a COM client (a SQL Server scheduled Job) using .NET and C#.

Step 1: Append to the System Path variable both .NET directory paths in order to run the .NET commands from any directory at the command prompt. The paths will vary according to the .NET Versions(1.0 or 1.1). These are the Version 1.0 paths that are used for this example on a Windows 2000 Professional system.

C:\WINNT\Microsoft.NET\Framework\v1.0.3705
C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin

>> Go to the Desktop.
>> Right-click on the My Computer icon.
>> Click on the Properties item in the context menu.

The System Properties dialog box will pop up
>> Click on the Advanced tab.
>> Click on the Environment Variables... button.
>> The Environment Variables dialog box will pop up.
>> Highlight the Path item in the System variables panel and click on the Edit button.

The Edit System Variable dialog box will pop up shown below.

>> In the Variable Value text box append both .NET path values to the value in the text box. First, insert a semi-colon (;) just after the last character in the text box, then append the .NET paths separated by a semi-colon to the value in the text box. See the semi-colons in red font below.

;C:\WINNT\Microsoft.NET\Framework\v1.0.3705;C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin

>> Click OK.
>> Click OK.
>> Click OK to get out of the three dialogs.

Now we can utilize the .NET command-line utilities from any directory of the command prompt.

Step 2: Create the assembly.

In order for SQL Server to be able to get to the assembly at runtime and use it as a COM object, the assembly has to be registered with COM, and it has to be either placed in the same folder as the host executable, or registered in the .NET global assembly cache (GAC) so that the runtime can find it. In order to add the assembly to the GAC, you will need to digitally sign it. To digitally sign it, you need a strong name key file, and will need to compile that key file into your assembly.

So let's see what it takes to do that. First, you create the strong name key file using the Strong Name Tool (sn.exe). You run sn.exe as follows to create a key file named createtxtkey.snk:

sn -k createtxtkey.snk

>> Add a test folder anywhere on your C: drive. I will call mine NetComJob.
>> Navigate to it through the command prompt.
>> Type in the above sn command and the following output will be seen.

>> Open up your text editor and copy the code below into it and save it as createtxt.cs in the NetComJob directory. Note the use of a public no-arguments' constructor. For this to be called from COM it has to have a default public no-arguments' constructor.

using System;
using System.Reflection;
using System.IO;
namespace nscreatetxt
{
public class clscreatetxt
{
public clscreatetxt()
{
}
public void createtxt()
{
StreamWriter SW;
SW=File.CreateText("c:\\MyTextFile.txt");
SW.WriteLine("Hello Mom");
SW.WriteLine("Hello GrandMom");
SW.Close();
}
}
}

You could use sn.exe and the Assembly Linker tool (al.exe) to add the key file to your assembly after you have compiled it, but a much easier way is to use the AssemblyKeyFile attribute in your code to compile it at compile time. It must be only declared once, and be at assembly scope (i.e., outside the namespace declaration for your code). For our example,

>> Add the following two lines to the top of the createtxt.cs file, immediately beneath all the "using" namespace statements and above the nscreatetxt namespace declaration. The AssemblyVersion attribute will be added also to control our versioning process. Save the file.

The two lines to be added.
[assembly: System.Reflection.AssemblyKeyFile("createtxtkey.snk")]
[assembly: AssemblyVersion("1.0.0")]

Insert these lines as below.

using System;
using System.Reflection;
using System.IO;
[assembly: System.Reflection.AssemblyKeyFile("createtxtkey.snk")]
[assembly: AssemblyVersion("1.0.0")]
namespace nscreatetxt
{
public class clscreatetxt
{
public clscreatetxt()
{
}
public void createtxt()
{
StreamWriter SW;
SW=File.CreateText("c:\\MyTextFile.txt");
SW.WriteLine("Hello Mom");
SW.WriteLine("Hello GrandMom");
SW.Close();
}
}
}

>> We need to compile the code into a .NET assembly. From the command line, type the following command:

csc /t:library createtxt.cs /r:System.dll

The /t (or /target) switch tells the C# compiler to make the resulting assembly a class library (or DLL) instead of using the default packaging of an EXE.

The /r (or /reference) switch tells the C# compiler to add references to the needed dlls.This output will follow.

Even though COM can find all the type information it needs to describe the component now, there is one last step before you will be able to actually instantiate the component from a client application. You must either copy the .NET assembly (createtxt.dll) into the same folder as the host executable that will be using it, or you must register the assembly in the GAC. I will take the latter approach to show how it is done.

Registering the assembly in the GAC is as easy as running one more command from the command prompt. The following output from the command prompt with the gacutil.exe tool.

>> Run the command: gacutil /i createtxt.dll

In the gac folder you can see the new assembly createtxt. Note the Version and Public Key Token.

The .NET framework provides a tool called regasm.exe, that you can use to create the necessary registry entries for your new assembly. It is simple to use, from the command prompt simply type: regasm [path to assembly]. The assembly will then be registered in the registry, just like a COM DLL. The next step now is to create the type library and register the assembly. You can create the type library with the tlbexp.exe tool, then call regasm.exe to do the registration, or you can do it all in one fell swoop using the /tlb switch on regasm.

>> Run the command: regasm createtxt.dll /tlb:createtxt.tlb

The following output after the regasm tool.

You now have a .NET COM component that can be consumed from any COM client. There is a lot more capability in .NET and the runtime to perform far more complex COM interoperability than the assembly described here but the same approach applies.

In the references dialog box of a project in VB6, you can see createtxt.dll, now available to COM. Note the Location value of the type library below.

In the Registry Editor (regedit.exe command) it is there below.

Step 3: Create the DTS in SQL Server.

>> Open up Enterprise Manager
>> Highlight the Data Transformation Services node of the local server
>> Right-click to see the context menu
>> Click on New Package to create a new DTS Package.

>> Click on the ActiveX Script Task button to create a script task for our package.

The ActiveX Script Properties dialog will appear as below.

>> Paste the following code into the scripting pane on the right.
>> Click OK at the bottom to exit this dialog.

Dim obj
obj = CreateObject("nscreatetxt.clscreatetxt")
obj.createtxt()

>> Click the Package menu tab as below.
>> Click the Execute item to see the successful notification below. Check the Write permissions to write to the chosen path as below.

>> Click OK to exit the Package Execution Results dialog box.
>> Click the Done button at the bottom to exit the Executing Package dialog.

See the Package Execution Results dialog.

>> Click the Package menu item to see its context menu.
>> Click the Save item to see the Save DTS Package dialog.

>> Name the package.
>> Click OK to save and exit the Save DTS Package dialog.

>> Now exit the DTS Package screen.

You will now see the newly named package in the Local Packages pane on the right.

Step 4: Schedule the package and create the SQL Server Job.

>> Select the new package, right-click, and click on the Schedule Package... item in the context menu.

The Edit Recurring Job Schedule dialog will appear.

>> Edit the new package appropriately and click OK to exit.

>> Proceed to the Jobs node, right-click and click Refresh.

When you schedule a DTS package in the above manner, a Job is created for you automatically in SQL Server. You can now see the newly created Job in the Jobs pane on the right.

You may edit the Job at any time by selecting the name of the Job, right-click, and click on the Properties item. The edit dialog, shown below, will pop up to edit appropriately.

The Job edit dialog box with its four tabs will appear after clicking on Properties.

The assembly here is very simple, i.e. creating a .txt file on the local machine. But one can quickly see the processing power here. The complexity and power lie in the requirements of the application. When ready to deploy this to a production SQL Server, the .NET Framework SDK must be installed on the server in order to access the .NET commands to build the assembly. Duplicate all these steps on the production server to deploy. An installation package could be created for deployment, but that is another article.