Upgrading SSIS Custom Components for SQL Server 2012

by Darren Green 12 Jun 2012 05:59
Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file. The high level steps are listed below, followed by some more details. Create a 2012 copy of the project file Upgrade project, just open the new project file is VS2010 Change target framework to .NET 4.0 Set conditional compilation symbol for DENALI Change any conditional code, including assembly versio... [More]

Creating packages in code - Workflow

by Darren Green 11 Jun 2010 18:08
This is just a quick one prompted by a question on the SSIS Forum, how to programmatically add a precedence constraint (aka workflow) between two tasks. To keep the code simple I’ve actually used two Sequence containers which are often used as anchor points for a constraint. Very often this is when you have task that you wish to conditionally execute based on an expression. If it the first or only task in the package you need somewhere to anchor the constraint too, so you can then set the expression on it and control the flow of execution. Anyway, back to my code sample, here’s a quick scre... [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]

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]

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]

Expression Date Functions

by Darren Green 28 Oct 2008 07:58
Date Parts Expressions support a range of date related functions such as DATEADD, with the same basic syntax to that found in T-SQL. Whilst the familiarity is very helpful, the difference that catches me out is the format of date part which must be quoted. T-SQL allows this: DATEADD(n, -10, GETDATE()) DATEADD(mi, -10, GETDATE()) DATEADD(minute, -10, GETDATE()) The SSIS equivalent is: DATEADD("n", -10, GETDATE()) DATEADD("mi", -10, GETDATE()) DATEADD("minute", -10, GETDATE()) Related functions that use the same date part tokens - DATEAD... [More]

Extending SSIS with custom Data Flow components (Presentation)

by Darren Green 6 Mar 2008 14:00
Download the slides and sample code from my Extending SSIS with custom Data Flow components presentation, first presented at the SQLBits II (The SQL) Community Conference. Abstract Get some real-world insights into developing data flow components for SSIS. This starts with an introduction to the data flow pipeline engine, and explains the real differences between adapters and the three sub-types of transformation. Understanding how the different types of component behave and manage data is key to writing components of your own, and probably should but be required knowledge for anyone bu... [More]

Generating Surrogate Keys

by Guest 4 Apr 2005 14:00
(By Jamie Thomson) Introduction Surrogate keys are generally considered fundamental building blocks of a data warehouse. They are used as identifiers for dimensional members and enable us to manage slowly changing dimensions. SSIS does not contain a built in component for generating surrogate keys but there is still a mechanism for doing it – the Script Component. The Script Component allows us to modify the data in a data flow path using managed code and we can use it to generate surrogate keys. The Row Number Transformation can be used to help generate surrogate keys... [More]

The Script Component as a Transformation

by Allan Mitchell 2 Aug 2004 14:00
In this article we are going to assume a few things about the reader as we want to concentrate as much as possible on the Script Component itself and not the peripheral stuff The things we will assume are: You are happy with adding a Data Flow task to the designer in Workflow You are happy to configure an OLE-DB Source adapter You are comfortable with configuring mappings in the Destination adapter As usual we will show you a graphic of the Data Flow once it is completed so we know that if yours resembles ours at the end then chances are we are at the same place. ... [More]