Search results matching tags 'SSIS' and 'Connect'

A potted SSIS history via Connect

jamiet — Thu, 07 Apr 2011 14:12:12 GMT

Yesterday I received a notification that one of my submissions to http://connect.microsoft.com had received a comment. Nothing unusual about that, I receive those sorts of emails every day, on this occasion however the comment was actually worth reading.

The comment was from SSIS development guru Jeff Bernhardt (he has changed job titles recently but I don’t know the new one so “dev guru” will have to do) and was posted in reply to my request for a single type system in SSIS (which I also blogged about last month at Have SSIS' differing type systems ever caused you problems?). Jeff’s comment provided a rather unique insight into some of the machinations within the SSIS product team as they go about triaging our many Connect submissions and also gave an interesting potted history of the SSIS product itself. For these reasons I thought it would be worth highlighting the comment to the larger SSIS community so with Jeff’s permissions I have copied the comment below (you can see the original at [SSIS] Consolidate three type systems into one)

…

The three type systems make me crazy too. They make all of the developers working on the guts of SSIS crazy. The history is interesting so I will share it:

The first part of SSIS that was built in the proto-days of 1999 was the replacement for the DTS import export wizard. I know, hard to believe. This was the first ‘host’ for the dataflow pipeline. The pipeline was built to use the OLEDB type system to make reading and writing from OLEDB really fast. Our internal buffers are really just OLEDB bound memory layouts. The DT_Foo type system is an exact copy of the OLEDB type system with some extra types added for SSIS specific use. That type system is built in deep to the dataflow and all transforms.

Next to arrive was the Runtime, we needed a real host for pipelines and a way to coordinate activities. In those days, the way software was built and extended here at Microsoft was to use COM and OLE Automation friendly interfaces. We expected that most folks that embedded and extended SSIS (then still called DTS) was to use native code and COM interfaces. Naturally, that is how the Runtime was designed and built; the COM type system is pervasive and runs deep inside the native Runtime. This is why we see the VARIANT types with BSTRs, SAFEARRAYS, etc. (as a fun note, the VARIANT type was design to copy the internal type system of Visual Basic, a VARIANT is a Var )

When it came time to build a designer for these interfaces (and believe it or not, the designer started a year or so after the internals were working) we took a bet on managed code and c#. This was a bet at the time; managed code was new and un-proven. There was a lot of anxiety. The UI is all managed code and so its chock full of the new and fancy CLR type system. We expected that some people might use managed code to host or extend SSIS so a lot of ‘wrappers’ were put together to make that work. Of course the seams on the type system show through.

So here we are with three type systems. Getting rid of the DT_Foo system would mean re-writing the pipeline (probably in managed code) and putting in a big shunting system to maintain backward compatibility with older transforms.

Getting rid of the VARIANT type system would mean re-writing the Runtime (probably managed) and providing some shunting and upgrade system for old packages and tasks.

Re-writing the UI is crazy talk, and would still need all of the back-compat layers put in.

So as much as we hate where we are, fixing this issue is a HUGE undertaking. For instance, it may have been the only work we took on for Denali. Certainly we thought about it, but the value delivered to customers of this one change is low when compared to all of the other things we could do for the same effort.

What does the future hold? It is hard to imagine us ever investing the effort to fix the SSIS type problem, mostly because of all of the backward compatibility issues that it would create. As we look to new problem spaces and new ways of solving problem in the cloud world, I expect the CLR type system will become the common language of data movement for us.

We will remember the pain that our short sighted decision making in 2001 unleashed on our devoted developer friends and we will strive to avoid making these mistakes again.

-Jeff Bernhardt

Have SSIS' differing type systems ever caused you problems?

jamiet — Mon, 14 Mar 2011 16:46:00 GMT

One thing that has always infuriated me about SSIS is the fact that every package has three different type systems; to give you an idea of what I am talking about consider the following:

The SSIS dataflow's type system is made up of types called DT_* (e.g. DT_STR, DT_I4)
The SSIS variable type system is based on .Net datatypes (e.g. String, Int32)
The types available for Execute SQL Task's parameters are based on something else - I don't exactly know what (e.g. VARCHAR, LONG)

Speaking euphemistically ... this is not an optimum situation (were I not speaking euphemistically I would be a lot ruder) and hence I have submitted a suggestion to Connect at [SSIS] Consolidate three type systems into one requesting that it be remedied. This accompanying blog post is not however a request for votes (though that would be nice); the reason is actually subtler than that. Let me explain.

I have been submitting bugs and suggestions pertaining to SSIS for years and have, so far, submitted over 200 Connect items. If that experience has taught me anything it is this - Connect items are not generally actioned because they are considered "nice to have". No, SSIS Connect items get actioned because they cause customers grief and if I am perfectly honest I must admit that, other than being a bit gnarly, SSIS' three type system architecture has never knowingly caused me any significant problems.

The reason for this blog post is to ask if any reader out there has ever encountered any problems on account of SSIS' three type systems or have you, like me, never found them to be a problem? Errors or performance degredation caused by implicit type conversions would, I believe, present a strong case for getting this situation remedied in a future version of SSIS so if you HAVE encountered such problems I would encourage you to leave a comment on the Connect submission accordingly. Let me know in the comments too - I would be interested to hear others' opinions on this.

@Jamiet

Newly closed Connect items auger well for SSIS in Denali

jamiet — Fri, 10 Sep 2010 11:56:00 GMT

Todd Mcdermid spoke recently on his blog post Integration Services vNext Coming Soon about how some recently closed Connect items had encouraged him as to the future of SSIS. Also, Matt Masson from the SSIS team has written a similarly encouraging blog post about some upcoming SSIS enhancements at Upcoming Product Changes.

Now its my turn. Just this morning I had three Connect items returned to me as "fixed" and, like Todd, I'm delighted to see these items getting closed as such. I have long complained about the shortcomings of SSIS's logging framework as I don't believe it produces enough "context" as to why a container happens to be executing; the consequence being that we end up getting lost in a plethora of log records where we can't see how each one relates to another. These three Connect items give me hope that this issue is getting addressed in the next version of SSIS (aka SQL11 aka Denali).

The three Connect items in question are:

Can't differrentiate between multiple instances of a task running in parallel - If a container happens to be running in parallel with another instance of itself (e.g. a dataflow task in a package that has been called from two Execute Package Tasks) there is no way to differentiate between the two. A solution would be to provide an Execution identifier for the execution of each container just like the ExecutionGUID that we get for a package.
Please put ExecutionGUID property on DtsContainer - Pretty much the same as the last one
SSIS: Make container stack available - This refers to what I call the context of a task being executed. We know that a task is executing but what are all the ancestral tasks and containers in the container hierarchy that have led to that task being executed?

The provision of more execution metadata for logging purposes gives me hope that the next version of SSIS will have a much better story around logging. Hopefully we won't have to wait long to find out.

@Jamiet

Be wary of using UNC paths in SSIS packages

jamiet — Thu, 19 Aug 2010 19:15:00 GMT

I have recently discovered what I believe to be a bug in SQL Server Integration Services (SSIS) 2008 and am taking the opportunity herein to warn about it so that nobody else gets stung by it.

The bug concerns the use of Uniform Naming Convention (UNC) paths inside a .dtsx package (i.e. paths that start with a “\\”). I have managed to reproduce a situation where a package will attempt to validate a File Connection Manager containing a UNC path even if the task(s) that use that Connection Manager have got DelayValidation=TRUE. In other words, the package may attempt to validate a Connection that will fail and this will cause errors in your package.

The first screenshot below shows the execution of my repro package. Notice:

When the package starts up @[User::FakeFileLocation] points to a non-existent drive "u:"

Also be aware of a few things that aren’t evident from the screenshot:

The ConnectionString of the "FILE" connection manager is set to @[User::FakeFileLocation]
The script task changes @[User::FakeFileLocation] to be the same as @[User::RealFileLocation]
The dataflow task, the connection manager & the package all have DelayValidation=TRUE

In this case everything works OK, no errors are thrown, everything is peachy.

Now take a look at this second screenshot. It shows the same package as before but with one important difference, @[User::FakeFileLocation] has been changed from u:\NonExistentFile to \\u\NonExistentFile. Notice in the output that we have some errors because SSIS has attempted to validate that UNC path; it did not attempt to do so before the change.

One other point of note is that these errors did not cause the package to stop executing however the error will still “bubble-up” to any calling package and will be interpreted as a package failure which is what makes this such a dangerous bug.

I have submitted the repro to Microsoft at https://connect.microsoft.com/SQLServer/feedback/details/585479/. No reply as yet.

@Jamiet

UPDATE: Microsoft have acknowledged the bug and have resolved to fix it in SQL11 (aka SQL Server Denali). See here for the following response:

We believe we found the cause of the problem. One of the system API we use to help resolve path can return different error codes depending on the network configurations and what are available.

We will fix the bug in Release 11 of SSIS.