SQL Server Integration Services is now, apparently, feature complete. Given that that is the case I started to ponder what would be nice to have in the next version and came up with a list of 30 or so things. I prioritised them and below are what I consider to be my 15 must-haves for the next version. They are all requests for new or modified functionality rather than simply aesthetic changes (apart from maybe #12 & #13).
Do you agree or disagree? Let me know in the comments section below!
- The ability to build libraries of data flows, pre-configured tasks and pre-configured transformations that can be instantiated in new packages. I have previously commented on this here.
- Ability to alter pipeline metadata at runtime thereby allowing us to build generic file-loading packages
- Crossjoin component
- Updates using the OLE DB Command component are too slow becaue they do row-based rather than set-based updates! James Howey from the SSIS dev team suggested a new SQL Server specific destination adapter that only does updates. In his own words: "it would seem worthy of investigation to consider an Update SQL Server Destination adapter, which creates a temp table at preexecute, fills it during execution, then issues the Update command postexecute." Couldn't agree more James!
- Prioritised destinations - If I have 2 destinations in a data-flow I may want to insist that one is populated before the other, perhaps if the second table has a FK to the first one (N.B. You can do this in Informatica)
- Data viewers currently only allow you to view the data in a path. What would be great is if we could issue SQL queries against the contents of the data viewers in order to further interrogate the data and uncover problems.
- Darren Green suggested that similar functionality could behave like a conditional breakpoint - break and show the data in the data viewer if my query/boolean expression is satisfied. Further adapting this idea, you could choose to only display the data viewer if the condition is met.
- Option on Auto-layout to have the graph laid out horizontally or vertically. Currently it is vertically only!
- Ability to UNDO an Auto-Layout because the results are sometimes worse than what you started with. In fact CTRL-Z on just about anything would be great.
- Ability to write variable values back out to a configuration file. I have blogged about this previously here and have included a scenario where it would be useful.
- Real-time monitoring tool for deployed packages
- Intellisense on expressions
- Colour coding of expressions - e.g. keywords highlighted in a different colour
- The ability to change a flatfile connection manager to a multifile connection manager (and vica versa), therefore keeping the configuration already applied.
- Ability to reference variables in a parent package without having to do it using a script task (a bit more information here).
Care to wager which (if any) of these will make it in? I know Donald already has a long list of ideas for the next version - I wonder if any of these are on it?
-Jamie