Sql-server – Calling a .NET method in a SQL Server script

sql serversql-clrsql-server-2008-r2ssis

In the data migration process, I need to convert some columns values form the source system before writing to the target table.

I've been provided with a Csharp code which reads a string and do the conversions (it is a complicated string manipulations) and then returns the converted string.

What would be the best way to plug this code in the data migration SQL script? I know little about CSahrp programming, and after researching the different sites, found out this can be done by either adding a script component in a SSIS package, or wrapping the .net code in a CLR function which can be called from SQL Server.

As my script is not part of a SSIS package, my preference would be defining a CLR function, but would be ok? What would be the pros and cons of each options?

Also do you think this goal can be achieved using another technique which is easier to do and maintain?

If you know a good link which shows me the process step by step, please share it with me 🙂

Thank you.

Best Answer

It would be a tough call between a CLR assembly and using SSIS given that your current migration script is just a script.

CLR Assembly:

  • Must be installed into a database (both the assembly binary and the exposed wrapper method(s)).
  • Requires setting up an external project in Visual Studio, something you may not be very familiar with.
  • Very easy to integrate into your existing script.
  • Debugging can be a bit involved, because you have to either debug locally (i.e., on the server), set up remote debugging, or set up a comprehensive test suite. All of these can be problematic for different reasons. (Note: setting up a test suite is a good idea, but if time is a factor and you've already received a tested chunk of code, it's much safer to skip it in this scenario than if you converted the code to T-SQL, for example.)

SSIS Script Component:

  • Means running a totally separate process to do the calculation.
  • Much easier to get started than with a separate assembly, where you have to provide all the boilerplate code. With SSIS, pretty much all you do is fill in the required method bodies.
  • BIDS, which is x86, can't debug x64 scripts, so there's some ridiculousness that comes from that. (See here for a quick fix.) This would allow you to debug locally (on your workstation), though, which is an advantage.

While I haven't tested, I'll go out on a limb and say that the performance of both methods should be similar. If performance is a big concern, set up both scenarios and comparison test. In fact, you may want to set up both anyway just for practice, so you can see some of the differences and similarities for yourself, which is way more interesting and fun than reading about it (well, at least I think so).

A third option would be to move the process entirely to SSIS, which is more suited to migration/ETL types of tasks. This may or may not be feasible. You wouldn't necessarily need to retool the whole process, as you can, of course, execute arbitrary scripts.

Finally, another option is to convert the code to T-SQL. Depending on the complexity, though, it may be quite a challenge. Certainly there are manipulation tasks much better suited to using a proper programming language/framework like .NET. Without seeing the code itself, it's impossible to say if it would even be worth an attempt. As I mentioned previously, you would definitely need to develop a comprehensive test suite in this case.

Personally, I would probably create a CLR assembly because I'm strong on the programming side of things and it would be the fastest, least intrusive way to integrate the code. But that's me, and I haven't seen the code, so it may not be the best solution for you and your environment.

Related Question