File Watcher Task

by Darren Green 6 Jun 2016 16:07
The task will detect changes to existing files as well as new files, both actions will cause the file to be found when available. A file is available when the task can open it exclusively. This is important for files that take a long time to be written, such as large files, or those that are just written slowly or delivered via a slow network link. It can also be set to look for existing files first (1.2.4.55). The full path of the found file is returned in up to three ways: The ExecValueVariable of the task. This can be set to any String variable. The OutputVariableName when specified. T... [More]

Using the Script Component as a Conditional Split

by Darren Green 12 Nov 2010 13:46
This is a quick walk through on how you can use the Script Component to perform Conditional Split like behaviour, splitting your data across multiple outputs. We will use C# code to decide what does flows to which output, rather than the expression syntax of the Conditional Split transformation. Start by setting up the source. For my example the source is a list of SQL objects from sys.objects, just a quick way to get some data: SELECT type, name FROM sys.objects type name S syssoftobjrefs F FK_Message_Pa... [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]

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]

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]

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS