SSIS Junkie : 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

Dynamic Unpivot : SSIS Nugget

jamiet — Sat, 29 May 2010 06:10:00 GMT

A question on the SSIS forum earlier today asked:

I need to dynamically unpivot some set of columns in my source file. Every month there is one new column and its set of Values. I want to unpivot it without editing my SSIS packages that is deployed

Let’s be clear about what we mean by Unpivot. It is a normalisation technique that basically converts columns into rows. By way of example it converts something like this:

AccountCode Jan Feb Mar

AC1 100.00 150.00 125.00

AC2 45.00 75.50 90.00

into something like this:

AccountCode Month Amount

AC1 Jan 100.00

AC1 Feb 150.00

AC1 Mar 125.00

AC2 Jan 45.00

AC2 Feb 75.50

AC2 Mar 90.00

The Unpivot transformation in SSIS is perfectly capable of carrying out the operation defined in this example however in the case outlined in the aforementioned forum thread the problem was a little bit different. I interpreted it to mean that the number of columns could change and in that scenario the Unpivot transformation (and indeed the SSIS dataflow in general) is rendered useless because it expects that the number of columns will not change from what is specified at design-time.

There is a workaround however. Assuming all of the columns that CAN exist will appear at the end of the rows, we can (1) import all of the columns in the file as just a single column, (2) use a script component to loop over all the values in that “column” and (3) output each one as a column all of its own. Let’s go over that in a bit more detail.

I’ve prepared a data file that shows some data that we want to unpivot which shows some customers and their mythical shopping lists (it has column names in the first row):

We use a Flat File Connection Manager to specify the format of our data file to SSIS:

and a Flat File Source Adapter to put it into the dataflow (no need a for a screenshot of that one – its very basic).

Notice that the values that we want to unpivot all exist in a column called [Groceries]. Now onto the script component where the real work goes on, although the code is pretty simple:

Here I show a screenshot of this executing along with some data viewers. As you can see we have successfully pulled out all of the values into a row all of their own thus accomplishing the Dynamic Unpivot that the forum poster was after.

If you want to run the demo for yourself then I have uploaded the demo package and source file up to my SkyDrive: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20100529/Dynamic%20Unpivot.zip The package was builton SSIS 2008.

Simply extract the two files into a folder, make sure the Connection Manager is pointing to the file, and execute!

Hope this is useful.

@Jamiet

FileNameColumnName property, Flat File Source Adapter : SSIS Nugget

jamiet — Wed, 31 Mar 2010 21:51:11 GMT

I saw a question on MSDN’s SSIS forum the other day that went something like this:

I’m loading data into a table from a flat file but I want to be able to store the name of that file as well. Is there a way of doing that?

I don’t want to come across as disrespecting those who took the time to reply but there was a few answers along the lines of “loop over the files using a For Each, store the file name in a variable yadda yadda yadda” when in fact there is a much much simpler way of accomplishing this; it just happens to be a little hidden away as I shall now explain!

The Flat File Source Adapter has a property called FileNameColumnName which for some reason it isn’t exposed through the Flat File Source editor, it is however exposed via the Advanced Properties:

You’ll see in the screenshot above that I have set

FileNameColumnName=“Filename”

(it doesn’t matter what name you use, anything except a non-zero string will work). What this will do is create a new column in our dataflow called “Filename” that contains, unsurprisingly, the name of the file from which the row was sourced. All very simple. This is particularly useful if you are extracting data from multiple files using the MultiFlatFile Connection Manager as it allows you to differentiate between data from each of the files as you can see in the following screenshot:

So there you have it, the FileNameColumnName property; a little known secret of SSIS. I hope it proves to be useful to someone out there.

@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

SSIS Package Stats Collector version 2

jamiet — Sat, 07 Nov 2009 01:58:24 GMT

A few weeks ago I published a blog entitled Collecting information about your SSIS packages which demonstrated a way that you could use T-SQL and XQuery to derive information about your SSIS packages. Since then I’ve made a few amendments to the script that accompanied that blog post as follows:

New columns

The collected data now includes the following information about your packages:
    PackageId
    PackageXML (The XML that forms the .dtsx file)
    NumberOfContainers
    NumberOfDataflows
    NumberOfConnectionManagers
    NumberOfVariablesInEntirePackage

New feature

Returns a list of non-unique GUIDs and the packages that they appear in. If a GUID appears more than once in the same package it tells us how many times

Bug fixes

If there were no tasks in the package no data would be returned. That is now no longer the case.
Previous version didn’t work on case-sensitive collations, should be OK now.

There are also more comments liberally sprinkled around it now as well!

Here’s a screenshot showing some of those new columns:

Returning [PackageId] as a field is an important addition because we can now very easily see if any of our packages share the same Package Id. Package IDs are supposed to be unique but if they are not then they are a problem waiting to happen and unfortunately Business Intelligence Design Studio (BIDS) doesn’t really protect you against it very well, in fact if you use package templates then its very likely that you have non unique GUIDs somewhere. (“Non unique globally unique identifiers” – that should set the alarm bells ringing if nothing else).

As alluded to above the script now returns a brand new dataset showing all the GUIDs that occur more than once across all of your packages. Now, I’m not just talking about PackageId GUIDs here, every single object in a SSIS package be it a task, container, component, variable, connection manager, column, whatever… has a GUID attached to it and given the healthy amount of copy-and-paste that goes on when developing SSIS packages its a sure bet that some of those GUIDs are going to get repeated somewhere. Of course, its up to you whether you want to do anything about it or not – if you’re a purist then the idea of repeated GUIDs might keep you awake at night although I suspect most people simply won’t care. Still, its there if you need it:

You may wonder why I have the [NumberOfTimeshisGUIDOccursInThisPackage] column in there. Well, its perfectly plausible that the a GUID can reoccur in the same package and in fact when I was testing this earlier on today I had a package that had 6 occurrences of a GUID because it contained 6 near-identical connection managers that had obviously been copied-and-pasted.

That’s it for now. Let me know if this script proves useful to you and let me know if there’s anything you’d like to see added to it. Download the latest version from: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091105/SSISPackageStatsCollector%5E_v2.sql. I have also dropped it into SQLMesh.

Looking forward to some feedback (hint hint).

@Jamiet

Collecting information about your SSIS packages [SSIS Nugget]

jamiet — Sun, 18 Oct 2009 21:08:00 GMT

Did you know that is is possible to read the contents of a SSIS package (i.e. a .dtsx file) from within SQL Server Management Studio (SSMS) using T-SQL? For example, take the following T-SQL snippet:

select    cast(BulkColumn as XML)
from    openrowset(bulk 'C:\tmp\MyPkg.dtsx',
                        single_blob) as pkgColumn;

It uses OPENROWSET to return the contents of a specified package (C:\tmp\MyPkg.dtsx) as an XML document. Here is a screenshot showing what this returns:

and clicking on that result opens up the following:

That’s what the inards of a .dtsx file look like. In other words we now have a queryable XML document representing a package, thereafter you’re limited only to what you can do with XQuery which is quite a lot. Now, I’m no XQuery expert by any means but I did manage to find a few uses for this. Here are some sample queries:

All properties of a package

SELECT    Props.Prop.query('.')                                                        as PropXml
,        Props.Prop.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            string(./@p1:Name)','nvarchar(max)')                    as PropName
,        Props.Prop.value('.', 'nvarchar(max)')                                        as PropValue
FROM    (
        SELECT    CAST(pkgblob.BulkColumn AS XML) pkgXML
        FROM    OPENROWSET(bulk 'C:\tmp\MyPkg.dtsx',single_blob) AS pkgblob
        ) t
CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                        /DTS:Executable/DTS:Property') Props(Prop)

I had a lot of help from @rbarryyoung with this one!

Name and type of every task in a package

SELECT    Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./p1:Property[@p1:Name=''ObjectName''][1]','nvarchar(max)') as TaskName
,        Pkg.props.value('declare namespace p1="www.microsoft.com/SqlServer/Dts";
                            ./@p1:ExecutableType','nvarchar(max)') as TaskType
FROM    (
        select    cast(pkgblob.BulkColumn as XML) pkgXML
        from    openrowset(bulk 'C:\tmp\Package.dtsx',single_blob) as pkgblob
        ) t
CROSS    APPLY pkgXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";
                            //DTS:Executable[@DTS:ExecutableType!=''STOCK:SEQUENCE''
                        and    @DTS:ExecutableType!=''STOCK:FORLOOP''
                        and    @DTS:ExecutableType!=''STOCK:FOREACHLOOP''
                        and not(contains(@DTS:ExecutableType,''.Package.''))]') Pkg(props)

Note that this one will also return all tasks that exist in eventhandlers and it ignores how “deep” a task is in the container hierarchy.

Putting it all together

Ok, that’s all pretty cool but it would be nice to combine it all together and get a summary of many packages, perhaps all of the packages on your machine. Hence I’ve put together a T-SQL script that will display summary information about all of the packages in a folder and its subfolders. Here’s the results of running that script on my dev machine:

217 packages – I’ve collected lot of them over the years! This shows a sampling of some of the information that it is possible to collect:

Package name
Original creator of the package
Package Type (signifies which version of BIDS was used to originally build it)
Version numbers
Number of tasks in the package

Want to know what your most complex package might be? Simply order this dataset in descending order of [NumberOfTasks]. Or maybe you want to know which of your developers has built the most packages – the answers are right here!

Of course, this could be extended to capture much much more information than what I have captured here. You may want to know how many eventhandlers each of your packages has, when the packages were created, or perhaps how many components are in your dataflows. The (SSIS) world is your oyster!!!

One thing that I thought would be very useful would be to stick this script into a SQL Agent job, run it on a daily basis, and insert the results into a history table thereby giving you a running history of all the packages in your system and when they changed. If anyone does do that let me know how it goes!

To execute the script simply open it and change the following line as appropriate:

DECLARE    @Path    VARCHAR(2000) = 'C:\*.dtsx';

Note that you will need to enable [xp_cmdshell] in order to run the script which is available on my SkyDrive at:

http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091018/SSISPackageStatsCollector.sql

If you experience any problems with it let me know. If you adapt it any way let me know that too because writing these XQuery statements is no easy task (believe me!!!) and it would be great to share that stuff with other people!

@JamieT

Update: The original version of this script only worked on SQL Server 2008. I have now updated it so that it works on SQL2005 also! Thanks to Bruce in the comments for alerting me to this fact/

Update 2: Found a few more issues and hence have uploaded another new version (same link still works though). Changes:

Script will now work on a server with a case-sensitive collation
xp_cmdshell is turned on at the top of the script
An error message that could get returned by the command-line call under certain circumstances wasn't getting handled. It is now!

Update 3: An updated version with bug fixes and new features is now available at SSIS Package Stats Collector version 2

Things you might not know about Precedence Constraints : SSIS Nugget

jamiet — Sat, 10 Oct 2009 22:11:06 GMT

Precedence constraints are one of the most oft used features of SQL Server Integration Services (SSIS) but also often one of the most overlooked. In this blog post I’ll cover a couple of things that you might not know about them.

The basics

Most people typically use precedence constraints like this, with a green arrow:

A green arrow here means “If ‘SEQ 1’ succeeds, execute ‘SEQ 2’”; that’s called on OnSuccess constraint. Like the DTS of old there are also OnError (Red) and OnCompletion (Blue) constraints which behave exactly as you would expect.

In SSIS you can also make precedence constraints conditional, so you could have an expression that states “Execute ‘SEQ 2’ IF ‘SEQ 1” is successful AND some condition is true as well”.

I’m sure most of you knew all that already, so here are a few things that you might not know.

Annotated Precedence Constraints

Firstly, did you know that precedence constraints can be annotated with the expression that is placed upon that precedence constraint? Each precedence constraint has a property called ‘ShowAnnotation’ and it has 5 possible values as depicted here:

Most of those options aren’t very useful but one of them, ‘Constraint Options’, can be very useful indeed if you have an expression on your constraint because it causes the expression to be displayed on your design surface like so:

Pretty neat! I am very much a fan of self-documenting packages and this is clearly a nod in that direction. I wish this were the default setting for this property but unfortunately it is not.

Precedence Constraint Scope

SSIS’s container hierarchy gives rise to the notion of scope. We are used to applying scope to a variable but its worth remembering that precedence constraints have scope as well. In the same way that every container* has a collection of variables, every sequence container, for loop container, for each loop container, eventhandler container and package container also has a collection of precedence constraints as is illustrated in this screenshot from MSDN:

Sequence Members (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.sequence_members.aspx)

*Remember, every task, eventhandler and package is also a container

Why is this important? Well, it means that every variable referenced in a precedence constraint must be scoped to the container of which the precedence constraint is a member (that’s a bit of a mouthful but read it bit by bit and hopefully it will make sense).

This is worth remembering because there is a bug in the SSIS Package Designer in BIDS which manifests itself as you not getting a design-time error if a variable in your precedence constraint expression is scoped to the container that the precedence constraint is coming from. Phew another mouthful, let me explain by referring to a picture.

In this example if the variable @[User::NumberOfRows] were scoped to task “SQL Prepare some data” you would not get a design-time error but you definitely would get an error when you executed the package. The bug is at design-time, not execution-time.

Wrap-up

Ok, there’s nothing here that is particularly earth-shattering but I think it helps to know this stuff. If you have any comments or questions please post them below.

@JamieT

AccountCode	Jan	Feb	Mar
AC1	100.00	150.00	125.00
AC2	45.00	75.50	90.00

AccountCode	Month	Amount
AC1	Jan	100.00
AC1	Feb	150.00
AC1	Mar	125.00
AC2	Jan	45.00
AC2	Feb	75.50
AC2	Mar	90.00