patterncsharpMinor
Should I use SSIS to write programming logic?
Viewed 0 times
ssisprogrammingwritelogicshoulduse
Problem
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#?
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#?
Solution
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:
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.
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:
- What language am I most comfortable with?
- 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.
Context
StackExchange Database Administrators Q#208754, answer score: 3
Revisions (0)
No revisions yet.