Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question
1  
If you show the actual CSharp code, someone can tell you whether it can be done easily in T-SQL or exactly how to create a CLR function to do so. What you've asked now is very difficult to answer due to lack of details - what exactly does the CSharp code do? How many rows are affected? – Aaron Bertrand♦ Oct 30 '12 at 3:10
As I had mentioned above the CSharp code provides a method that gets a string, manipulates it based on a complex logic and returns the converted string. This new string will be loaded in the target table. So I get a string (column value) from a source table, run the CSharp method (somehow) and writes the converted value to the target table. The number of source records are to be converted and loaded would be around 20,000. – Nazila Beikpour Oct 30 '12 at 5:09

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.