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

[ronc85]

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

 

[Cowboy \(Gregory A. Beamer\)]

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).

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

*************************************************
| Think outside the box! |
*************************************************

 

[ronc85]

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

 

[Aidy]

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

 

[Guest]

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

 

[ronc85]

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