Have you used the ExecutionValue and ExecValueVariable properties?

by Darren Green 4 May 2010 08:36
The ExecutionValue execution value property and it’s friend ExecValueVariable are a much undervalued feature of SSIS, and many people I talk to are not even aware of their existence, so I thought I’d try and raise their profile a bit. The ExecutionValue property is defined on the base object Task, so all tasks have it available, but it is up to the task developer to do something useful with it. The basic idea behind it is that it allows the task to return something useful and interesting about what it has performed, in addition to the standard success or failure result. The best example perh... [More]

Design Patterns for SSIS Performance (Presentation)

by Darren Green 21 Apr 2010 18:53
Here are the slides from my session (Design patterns for SSIS Performance) presented at SQLBits VI in London last Friday. Slides - Design Patterns for SSIS Performance - Darren Green.zip (86KB) It was an interesting session, with some very kind feedback, especially considering I woke up on Friday without a voice. The remnants of a near fatal case of man flu rather than any over indulgence the night before I assure you. With much coughing, I did turn the off the radio mic during the worst, and an interesting vocal range, we got through it and it seemed to be well received. Thanks to ... [More]

Hosting woes

by Darren Green 21 Apr 2010 18:39
Unfortunately quite a few people have noticed our recent hosting problems, but if you are reading this they should all be over, so please accept our apologies. Our former web host decided migrate to a new platform, it had all sorts or great features, but on reflection hosting wasn’t one of them. We knew it was coming, and had even been proactive and requested several dates on their migration control panel so I could be around to check it afterwards. The dates came and went without anything happening, so we sat back and carried on on for a couple of months thinking they’d get back to us when t... [More]

Where is my app.config for SSIS?

by Darren Green 3 Aug 2009 09:20
Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this. You are using referencing an assembly in a Script Task that uses Enterprise Library (aka EntLib), so you need to add the relevant configuration sections and settings, perhaps for the logging application block. You are using using Enterprise Library in a custom task or component, and again you need to add the relevant... [More]

Exploring packages in code

by Darren Green 17 Jul 2009 16:07
In my previous post Searching for tasks with code you can see how to explore the control flow side of packages, drilling down through containers, task, and event handlers, but it didn’t cover the data flow. I recently saw a post on the MSDN forum asking how to edit an existing package programmatically, and the sticking point was how to find the the data flow and the components inside. This post builds on some of the previous code and shows how you can explore all objects inside a package. I took the sample Task Search application I’d written previously, and came up with a totally pointless l... [More]

New SSIS tool on Codeplex – SSIS Log Analyzer

by Darren Green 10 Jul 2009 12:19
I stumbled across a new SSIS tool on Codeplex today, the SSIS Log Analyzer which was only released a few days ago. Whilst it is a beta release and currently only supports 2005 (2008 is promised) it looks quite interesting. It seems to be a fancy log viewer, but with some clever features and a nice looking front-end. I’ve only read the documentation so far, but it has graphs and a debug view that shows your package with the colour animations similar to when debugging in BIDS, and everyone knows, the way the pretty colours and numbers change is the best bit! I’ll quote some of the features for ... [More]

Downloading a file over HTTP the SSIS way

by Darren Green 2 Jul 2009 18:14
This post shows you how to download files from a web site whilst really making the most of the SSIS objects that are available. There is no task to do this, so we have to use the Script Task and some simple VB.NET or C# (if you have SQL Server 2008) code. Very often I see suggestions about how to use the .NET class System.Net.WebClient and of course this works, you can code pretty much anything you like in .NET. Here I’d just like to raise the profile of an alternative. This approach uses the HTTP Connection Manager, one of the stock connection managers, so you can use configurations and prop... [More]

Logging connection strings

by Darren Green 10 Jun 2009 21:44
If you some of the dynamic features of SSIS such as package configurations or property expressions then sometimes trying to work out were your connections are pointing can be a bit confusing. You will work out in the end but it can be useful to explicitly log this information so that when things go wrong you can just review the logs. You may wish to develop this idea further and encapsulate such logging into a custom task, but for now lets keep it simple and use the Script Task. The Script Task code below will raise an Information event showing the name and connection string for a connection... [More]

SSIS Tips & Tricks (Presentation)

by Darren Green 21 May 2009 09:01
This has been a rather well used presentation title but it does allow a certain degree of flexibility, and we covered a good range of topics in my session at the UK SQL Server User Group in Cambridge last night. Thanks to all who attended. Here is the rather limited slide deck and the all important demo packages for download as promised. For reference, high level topics covered were BIDS Helper Inserts and Updates Transactions Script Debugging Data Flow Checkpoints I’ll update the post with a link to the Live Meeting recording when I get it. Presentation & Demo ... [More]

Creating packages in code – Execute SQL Task

by Darren Green 19 May 2009 08:30
The Execute SQL Task is for obvious reasons very well used, so I thought if you are building packages in code the chances are you will be using it. Using the task basic features of the task are quite straightforward, add the task and set some properties, just like any other. When you start interacting with variables though it can be a little harder to grasp so these samples should see you through. Some of these more advanced features are explained in much more detail in our ever popular post The Execute SQL Task, here I’ll just be showing you how to implement them in code. The abbreviated cod... [More]

Searching for tasks with code – Executables and Event Handlers

by Darren Green 12 May 2009 09:40
Searching packages or just enumerating through all tasks is not quite as straightforward as it may first appear, mainly because of the way you can nest tasks within other containers. You can see this illustrated in the sample package below where I have used several sequence containers and loops. To complicate this further all containers types, including packages and tasks, can have event handlers which can then support the full range of nested containers again. Towards the lower right, the task called SQL In FEL also has an event handler not shown, within which is another Execute SQL Task,... [More]

Maintaining packages with code - Adding a property expression programmatically

by Darren Green 7 May 2009 14:15
Every now and then I've come across scenarios where I need to update a lot of packages all in the same way. The usual scenario revolves around a group of packages all having been built off the same package template, and something needs to updated to keep up with new requirements, a new logging standard for example.You'd probably start by updating your template package, but then you need to address all your existing packages. Often this can run into the hundreds of packages and clearly that's not a job anyone wants to do by hand. I normally solve the problem by writing a simple console applicat... [More]

Creating packages in code - Package Configurations

by Darren Green 2 Mar 2009 10:00
Continuing my theme of building various types of packages in code, this example shows how to building a package with package configurations. Incidentally it shows you how to add a variable, and a connection too. It covers the five most common configurations: Configuration File Indirect Configuration File SQL Server Indirect SQL Server Environment Variable  For a general overview try the SQL Server Books Online Package Configurations topic. The sample uses a a simple helper function ApplyConfig to create or update a configuration, although in the example we will o... [More]

Creating packages in code - Flat File Source to OLE-DB Destination (SQL Server)

by Darren Green 23 Feb 2009 13:00
This code sample programmatically creates a package that imports a text file into SQL Server, with a Flat File Source and the OLE-DB Destination. This shows how you can leverage the SSIS engine to write your own data import tool for example, but beware importing files is not as simple as it may seem. When you build a similar package in the designer and and select your file, you make some choices about the file format. It seems quite simple, but there is actually some quite complex thinking behind the scenes to parse the file and make suggestions on the file format for columns and their data ... [More]

CreationName for SSIS 2008 and adding components programmatically

by Darren Green 2 Feb 2009 16:14
If you are building SSIS 2008 packages programmatically and adding data flow components, you will probably need to know the creation name of the component to add. I can never find a handy reference when I need one, hence this rather mundane post. See also CreationName for SSS 2005. We start with a very simple snippet for adding a component: // Add the Data Flow Task package.Executables.Add("STOCK:PipelineTask"); // Get the task host wrapper, and the Data Flow task TaskHost taskHost = package.Executables[0] as TaskHost; MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject... [More]

Creating packages in code - OLE-DB Source to Flat File File Destination

by Darren Green 29 Jan 2009 18:02
This code sample programmatically creates a package with an OLE-DB Source and a Flat File Destination, and the resulting package exports data from SQL Server to a CSV file. The finished package just has the one Data Flow Task shown below.   The code creates the package, configures the task, and components, then saves the package to disk, useful for checking the package and testing, before finally executing. namespace Konesans.Dts.Samples { using System; using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using RuntimeWrapper ... [More]

Creating packages in code - OLE-DB Source to Raw File Destination

by Darren Green 29 Jan 2009 10:13
This code sample programmatically creates a package with an OLE-DB Source and a Raw File Destination, and the resulting package exports data from SQL Server to a raw file. The finished package just has the one Data Flow Task shown below.   The code creates the package, configures the task, and components, then saves the package to disk, useful for checking the package and testing, before finally executing. namespace Konesans.Dts.Samples { using System; using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; internal class SqlTo... [More]

CreationName for SSIS 2005 and adding components programmatically

by Darren Green 29 Jan 2009 09:07
If you are building SSIS 2005 packages programmatically and adding data flow components, you will probably need to know the creation name of the component to add. I can never find a handy reference when I need one, hence this rather mundane post. See also CreationName for SSS 2008. We start with a very simple snippet for adding a component: // Add the Data Flow Task package.Executables.Add("STOCK:PipelineTask"); // Get the task host wrapper, and the Data Flow task TaskHost taskHost = package.Executables[0] as TaskHost; MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject... [More]

Sorting MSDB package folders

by Darren Green 7 Jan 2009 19:20
If you use the MSDB package store in SSIS 2005, you may have noticed that the folders are not sorted in any friendly way, it is actually just the order they were added. You end up with something like the left-hand example below. The right-hand example shows the same SSIS instance after my suggested update, and even though I only have a few folders and packages just to illustrate the point, it looks much better and easier to use. Before     After     By tweaking... [More]

Backup or transfer SSIS Packages

by Darren Green 3 Nov 2008 22:04
How can you backup your SSIS packages? I've been asked several times, and the answer is it depends. Where do you store your packages? SSIS Package Store The SSIS package store is just a folder on disk, so regular file system backups should suffice, or you can backup that folder specifically. By default it is %ProgramFiles%\Microsoft SQL Server\90\DTS\Packages. It is possible that multiple folders can be used, or the default is changed. This can be explored further by reviewing the SSIS service configuration file %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. For more in... [More]

RecentComments

Comment RSS