SSIS looks
interesting. I haven't loaded any Yukon or read the 2005 BOL, so I'm coming in with a clean slate.
In the past I have found the graphical programming interface for DTS, limiting, annoying and unnatural. I much prefer coding in an OO language the conventional way.
I'm interested in evaluating SSIS as quickly as I can. The ideal is programming with a standard .NET language. I am happy if a design surface gives me a start but I expect to go into the code it writes and continue with a standard code editor.
What is the best reference to get a idea whether this is sensible to do and what the programming experience is like?
Thanks.That still isn't the experience. While SSIS has made great strides over DTS in terms of functionality, i.e. the ability to easily do looping constructs, etc it is still not like writing a Windows Forms application where you write code to perform various operations. The script transform is the exception to this but I have found myself using it very rarely.|||Thanks for that Chris.
I'm aware of a way of programmatically creating a DTS task. (I never used it, for real work, but I've read interesting write ups. If I remember correctly it involved exporting the package as a VBScript, that created the task by instantiating and using the underlying DTS object model. These VBScript files could then be used as the basis for creating a VB or VB.NET program, though this was probably not done often.)
I assumed that SSIS would, at least, retain that functionality, but with a .NET language in place of the VBScript. (Though I was hoping for something a lot better.)
(I really like the idea of a live diagram showing the code, but I still want to be able to access that code directly.)|||I can't seem to find a way to export a package like you used to be able to do in DTS. Doesn't mean that it isn't possible. Just isn't obvious to someone who has spent around 150 hours in the tool.|||That feature is not supported on SSIS.
K|||Thanks Kirk.
I don't understand that design decision. It cuts the traditional style of programming out of the loop. I was really looking forward to improved access to the ETL engines. (I guess that means carry on with other approaches to ETL in the CLR 2.0 era!)|||You still have a choice how how you build packages. Designer Vs Code.
You can still create/load and execute packages through code. What you cannot do is create a package and "script" it, in effect Save As VB as we had in DTS. It's a shame, since it was a great way to get a head start on creating a package in code, but at the end of the day not a major problem really.
I don't understand the comparison of SSIS with traditional style programming, since at the end of the day this is a ETL tool. You can access the objects through code if you want, but there will be limitations using the object model since it is designed to do a specific job, it is not a programming language.
On the programming theme, the ability to develop your own components for SSIS is so much easier, and more open, compared to DTS. You can write connection managers, tasks, pipeline components (a bit like old transforms), log providers and enumerators.|||Thanks Darren.
That revives my interest.
Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!)
As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.)|||
 MikeGale wrote: |
|
| Thanks Darren. That revives my interest. Recently I have given up using DTS in favour of coding the whole process. So, in my mind, I see a continuum between code and ETL tool. I think the design decision is driven by the idea of who the target user is. It's assumed the user is "Somebody who is not going to write conventional code". (In that case those who are comfortable writing code, kinda get driven out.) (The decision might also be based on one guy to do DTS, one guy to do code, one guy to test... which in my view is a recipe to turn a 2 man-hour job into a 9 man-day job!!) As you say the loss of the ability to generate code from a wizard made package is unfortunate. The improved ability to write your own transforms (etc.) looks like it could have fixed an Achilles Heel of DTS. (It was an horrendous job to write script to perform real world serious transforms.) |
Mike,
Don't discount the ability to write custom transformation functionality using the script component and the script task - they are fantastically powerful bits of kit and execute managed code rather than script code so its many times more efficient than ActiveX in DTS.
Donald Farmer has book coming out dedicated solely to scripting in SSIS and exactly what can be achieved. http://www.amazon.co.uk/exec/obidos/ASIN/1932577211/202-4310542-9360621?%5Fencoding=UTF8
-Jamie
