Search results matching tags 'Video' and 'nugget'

SSIS gotcha: Beware of multiple outputs from a synchronous script component

jamiet — Sun, 04 Jul 2010 20:43:12 GMT

A few days ago I was alerted to a peculiarity of SSIS’s Script Component that I believe people need to be aware of. Its a peculiarity that can rear its head when your script component is synchronous and has multiple outputs. Here’s an example of a dataflow that contains such a component:

Some things to note about this dataflow that you can’t tell from this screenshot:

The source component will create a 1-row,1-column dataset with the value “1” in it
Even though it has three outputs “SCR Send data to multiple outputs” is still a synchronous component*

*If you don’t understand how a synchronous component can have multiple outputs and/or have never heard of an Exclusion Group then you should go and read my blog post from 2005 Multiple outputs from a synchronous script transform – the blog post you’re reading right now won’t make much sense without it! If you have ever used the Multicast component then it shouldn’t surprise you that a synchronous component can have multiple outputs – because that’s a pretty good definition of what the Multicast component does!

Here’s the important code inside “SCR Send data to multiple outputs”:

The code:

increments the incoming value and puts it into Output0
increments the value again and puts it into Output1
increments the value again and puts it into Output2

Given that out starting value is “1” you might expect that our three outputs would contain the values “2”, “3” & “4” respectively but in fact that is not the case. Here is what we actually see in those three outputs when we execute the dataflow:

Each of our outputs has the same value “4”. Why is that? The trick is in understanding something fundamental about synchronous components, they only ever output the same number of rows as are input. The fact that in the data flow above it appears as though three rows have been output is simply an illusion that is best explained by former SSIS Development Manager Kirk Haselden who left the following comment on my blog post that I linked to earlier:

The additional or duplicate rows are an illusion. The Dataflow Task actually tracks what buffer columns and rows are visible to the downstream transforms, but doesn't copy any buffers or rows. It simply "exposes" them with row and column views.
Truly, the synchronous outputs only send the same number of columns* as they receive on their inputs.
- Kirk Haselden, 15th March 2006
*This is a typo. Kirk meant to say “rows” not “columns”

In our case the script component has simply incremented the same value three times and we are looking at that same incremented value of “4” in each of our outputs.

I have produced a short video that demonstrates this behaviour using the dataflow pictured above. Embedding videos here on SQLBlog is however a fiddly experience so for ease I’ll just direct you to view it on Vimeo at Multiple Outputs from a synchronous script component.

Hope this helps. Any questions please put them in the comments.

@JamieT

Parsing flat files using SSIS : SSIS Nugget

jamiet — Wed, 24 Mar 2010 23:11:00 GMT

Often when using SQL Server Integration Services (SSIS) you will find there is more than one way of accomplishing a task and that the most obvious method of doing so might not be the optimal one. In the video below I demonstrate this by way of an experiment using SSIS’s Flat File Source component; I show different ways that you can pull data from a flat file into the SSIS dataflow and also how the nature of the data itself can influence your choice as to how this task should be accomplished.

If you are having trouble viewing the video in your blog reader then head to http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/25/parsing-flat-files-using-ssis-ssis-nugget.aspx to see it as it is hosted on my blog!

The main point I want to get across from this video is that a little bit of creative thinking when building your dataflows can sometimes be very beneficial for performance; quite often building a solution that isn’t the most obvious might actually turn out to be the best one.

You’ll notice, if you have watched the video, that my editing skills weren’t quite up to snuff and I cut off the final few words however all I was saying was that if you have any feedback on this video then I would love to hear it either via email or preferably the comments section below. I hope this turns out to be useful to some of you.

@Jamiet

P.S. Incidentally the parsing that we do using SSIS expressions in the video would be much easier if we had a TOKENISE function in SSIS’s expression language and I have asked for the introduction of such a function on Connect at [SSIS] TOKEN(string, tokeniser_string, occurence) function. Feel free to go and vote that up if you think this feature would be useful!

Destination Adapter Comparison : SSIS Video Nugget

jamiet — Tue, 02 Mar 2010 12:11:00 GMT

In this, my latest video nugget, I demonstrate and compare three ways that you can insert data into a SQL Server table from the SSIS DataFlow:

OLE DB Destination without Fast Load
OLE DB Destination with Fast Load
SQL Server Destination

The demo peels back the covers to see how these three methods actually operate against the database. The video is embedded below however at the time of writing we are still having issues with getting videos to show up on SQLBlog meaning that the video may appear too small to view hence in the meantime I’ll refer you to view the video on Vimeo at http://vimeo.com/9806121 (it is 7m19s long).

If after watching this you want to know any more then you may glean some useful information from my blog post SSIS: Destination Adapter Comparison from August 2006 or feel free to post any questions in the comments below and I’ll do my best to answer them!

Hope this is useful!

@Jamiet

Outputting data to an XML file : SSIS video nugget

jamiet — Sat, 27 Feb 2010 16:53:00 GMT

The last video nugget that I produced seemed to be fairly well received so I thought I would attempt a few more; and indeed, here is the next one. In this video I demonstrate a simple technique for outputting data to an XML file using SSIS’s Script Task.

The video is 8m22s long and is embedded below however I’m aware of some problems that we are having, at the time of writing, with embedding of videos here on SQLBlog so for the time being head over to http://vimeo.com/9781950 where it can be viewed.

If you are so inclined I have made the package that I built whilst recording this demonstration available on my SkyDrive for your reference.

I look forward to your comments.

@Jamiet

Looping over SQL scripts and executing them : SSIS video nugget

jamiet — Wed, 24 Feb 2010 23:02:00 GMT

Earlier today while surfing the SSIS forum on MSDN I noticed a question that asked how one could execute a series of .sql files using SSIS. One suggested approach was to read the contents of the file(s) into a variable using a Script Task and then execute the contents of that variable using an Execute SQL Task. Well, that would work but there is actually a much quicker and easier way and given that at least two people on that thread didn’t know about it I thought it might be worth putting together a little demo.

I’ve produced a video, embedded below, that demonstrates this technique. Quite simply it loops over the .sql files in question and points the Execute SQL Task directly at those files which saves us from having to read the file contents into a variable. The video is 5m31s long:

If the video is not showing up for whatever reason then you can go and view it over at http://vimeo.com/9714659.

That’s it, hope it was useful! Over on my old blog I used to produce a lot of these how-to blog posts and I termed them SSIS Nuggets – I liked the connotation of them being bite-sized bits of info that you could easily consume. I’d like to get back into producing SSIS nuggets and I reckon that videos like this could be a useful way of communicating them. Does that sound like a good idea? Let me know in the comments!

@Jamiet