How to call C# dll from sql server 2000 dts?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ronc85@gmail.com - 16 Aug 2007 02:35 GMT

Using SQL Server 2000, Visual Studio 2005, .NET 2.0 and C#.

>From a SQL Server 2000 DTS package is it possible to call a C# dll

passing one parameter and having two parameters returned?

Thanks,
Sal

Reply to this Message

Cowboy (Gregory A. Beamer) - 16 Aug 2007 05:18 GMT

You can NEVER return two parameters. You can return XML, or similar, and
parse. Or you can pass one parameter by reference and alter it, but your
return type is always singular.

Having said that ... there are some possibilities here. The issue here is
getting DTS to talk to anything, as it likes scripting. Move up to SSIS
(2005) and some of the problem goes away.

But, it is possible to hit COM. And, that means if you COM wrap .NET
components you can use them. Have never tried, so there is no way I can give
you a step by step. With SQL 2000, you can export the package to VB and then
call the COM wrapper from there, so I know that is possible, but it is also
a pain to do if you move the package out to VB too early, as editing DTS in
COM is painful (did it once .... once).

Signature

Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************

| Think outside the box! |

*************************************************

> Using SQL Server 2000, Visual Studio 2005, .NET 2.0 and C#.
>
[quoted text clipped - 3 lines]
> Thanks,
> Sal

Reply to this Message

ronc85@gmail.com - 16 Aug 2007 06:15 GMT

Unfortunately switching to 2005 is not possible at this time.
Returning one parameter would work. I just haven't seen an example of
how to create a COM object with .NET 2.0 that is accessible with DTS
or an extended stored procedure.

Thanks,
Sal

Reply to this Message

Aidy - 16 Aug 2007 10:16 GMT

> I just haven't seen an example of
> how to create a COM object with .NET 2.0 that is accessible with DTS
> or an extended stored procedure.

It's outside the scope of this group, but google .net and com and you'll
find loads of examples on the net. It's very simple, just create a normal
class with an interface and fixed guid, compile it and use the command line
tool "regasm" to create and register a COM wrapper for you. You can now
call it via your DTS script using;

set obj = CreateObject("MyProject.MyClass")
obj.MyMethod ()

Quick search on "exposing .net class com" found this

http://www.codeproject.com/dotnet/nettocom.asp

Reply to this Message

ronc85@gmail.com - 17 Aug 2007 03:09 GMT

Thanks. I forgot this was the ASP.NET forum. Most of my time is with
ASP.NET/AJAX.

Reply to this Message

Sergey Poberezovskiy - 17 Aug 2007 02:13 GMT

One way would be to COM enable your C# dll, register it on the same box where
your SQL Server runs - then you should be able to use it the same way as any
other COM objects inside ActiveX scripting task

> Using SQL Server 2000, Visual Studio 2005, .NET 2.0 and C#.
>
[quoted text clipped - 3 lines]
> Thanks,
> Sal

Reply to this Message

Rate this thread:

How to call C# dll from sql server 2000 dts?

Free Magazines