SSIS, .NET and a small license fee..
Well then, as it seems, the so called SQL Server Integration Services can provide very good integration.. but at a very modest price … of course says Microsoft.
Recently I went through a painful discovery that you cannot invoke a local SSIS package and command it to execute it on a specific SSIS Server. In fact you cannot command an SSIS package to execute on a specific SSIS server at all. This in itself I think is a rather big limitation of SSIS. Subsequently, that package will only execute locally, and of course, you will then need Integration Services installed, which costs money.
So in a distributed architecture where ther is physical separation between your servers (web, database, reporting, SSIS), then to achieve integration with SSIS would cost you a arm and a leg.
And surely many articles and posts that I have read suggest to use a remote component or web service to invoke packages that stored on the SSIS server, but that entirely defeats the purpose of a distributed architecture because it would require a web server or .NET application on an SSIS server. Ridiculous.
Imagine this. You have a .NET / ASP.NET application that generates and executes a query on a SQL database million miles away. And now imagine you are required to have SQL Server installed on every client that is running that application. You’d be dumbfounded. So why is it so different with Integration Services?
So simply, SQL Integration Services does not integrate very well in distributed architectures. Ironical. They should call it SQL Distributed Services.
P.S. For the benefit of anyone who’d like to know – You cannot execute a SSIS package from your .NET application using ManagedDTS unless:
1. Your .NET app is on the same box as integration server, or
2. You have enough bling to install integration server wherever your .net app runs, or
3. You have no objection to installing and running a remote component or web service in your integration server.
Of course you can use agent jobs or command line, but they won’t be ‘managed’ in your .NET app.
P.S.S. There are some misleading posts on the internet that suggest you can get away with just having the SQL Server client tools installed. This is incorrect – don’t waste your time trying this, I already did.
