2

I have come across a fairly complex system where the business logic is written in SSIS. Since SSIS is not able to perform a big portion of this logic, it is heavily dependent on C# script tasks to perform the operations.

To give you an example of things that requires C# Script task, the application requires making API calls to a 3rd party application for getting the data and also requires a third party library for performing some calculations on the data.

Calling out to C# script tasks makes development and debugging more difficult... but at the same time, SSIS allows programmer to make use of C# script tasks.

  • What are the applications of SSIS?

  • Is there any benefit in implementing a complex logic using SSIS + C# Script Tasks? If we need to use C# Script , then why not write the whole logic in C#?

3
+50

To answer your question directly, if you know T-SQL and c# SSIS can do pretty much anything you need. My personal beef with it as a platform however stems from the fact the it tends to deal with data procedurally, instead of as a set. This is important because it's where RDMS's shine, so you're cutting off one of your best resources off at the knees. From my perspective, despite it's vast capabilities, it's more of a platform for people who aren't comfortable programming.

To answer your question less directly, I found myself in a very similar situation 12 months ago. I found it useful to start by asking myself these two questions:

  1. What language am I most comfortable with?
  2. And what environment am I most comfortable in?

After answering these questions, I chose to build my solution using those tools which enabled my skills.

In my case the answer was T-SQL and SSMS. Thus, I elected to remove the dependency on SSIS completely, and went the custom T-SQL/Agent route. In the end, I couldn't be happier with the decision. Maintaining & debugging the solution is a simple task, since I'm so familiar with the language an environment.

2

It is subjective. Every answer is just someone's opinion but, well, this is my answer.

I use SSIS for ETL only. SSIS script is too hard to use.

While some logic takes 5 minutes to implement in C# it might take you days with SSIS. On top of that, if the logic needs to loop or must be performed on each row it will be much faster to do it with C#.

A database is good for set based operations and very bad at loop or individual row processing.

If I were to overly simplify things, don't do anything that needs to declare a variable in SSIS. But as I said a thing like this is subjective.

The trade-off for using C# is that you need to maintain additional code.

2

Community wiki answer:

Go with whatever floats your boat, meets the business needs, and your team are able to maintain and support.

There are always going to be upsides and downsides to different solutions and technologies where some may think it's more complex, while others may think it is easier because that is what they are used to, etc. Use all the tools from the toolbox if you can, and don't get stuck in the mind-set of only using one tool from a toolbox with many tools, or else you may never know what you are missing.

Many large environments use SSIS and C# logic, with slews of stored procedures, many SQL Agent jobs, and Powershell too. There may also be custom apps doing various things as well, with many instances of SQL Server.

This is all incredibly subjective. If you have a team of C# wizards and no one is fluent in SSIS, then complex business logic in SSIS is going to be painful. If you're a heavily data-driven shop, and your business logic encompasses several data sources, then you might benefit from putting more in SSIS.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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