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]

SQLBits IV session voting open

by Allan Mitchell 27 Jan 2009 11:36
We've now closed session submission for SQLBits IV, which will be taking place on March 28th in Manchester. Once again we've had a great response and it's now time to vote for which of the 83 submitted sessions you'd like to see; to do this you need to register on the site and then go to and choose the sessions you'd like to see. Darren and I have both submitted sessions.

Data Mining Time Series presentation

by Allan Mitchell 22 Jan 2009 13:15
Here are the slides/Spreadsheets and queries I ran in my UK User Group presentation.  I talked about how the algorithms in SQL Server 2008 (ARTxp and ARIMA) work and what the effects of changing some of the attributes are on predictions. It was a great night and I got some good feedback. Hope you enjoy Time Gentlemen (178.59 kb)

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]

UK BI User group Meeting Next Week

by Allan Mitchell 6 Jan 2009 20:55
OK, this is rather short notice I know but the UK SQL Server User Group is hosting a BI evening event next week, on January 15th at Microsoft's HQ in Reading:   Anyway if you follow the link you'll see that I'm one of the speakers: I’ll be doing a session on Data Mining and the Time Series Algorithm.Chris Webb will be repeating the session he did at PASS last year on building a monitoring solution for Integration Services, Analysis Services and Reporting Services. Hope to see y... [More]

SQLBits IV Session Submission now open.

by Allan Mitchell 27 Dec 2008 13:40
  The next installment of the increasingly popular community conference (FREE) is going to take place in Manchester on 28.03.2009 (Saturday).  This is an excellent place for you to get some experience in speaking and telling a couple of hundred people something about SQL Server which really rocks your world and you want to let people know or to learn something new yourself. We will be there as we have been for all the SQLBits events.  We really enjoy the day and love to meet the people who visit our blog so… Submit a session here We also would love to hear from anyone that ... [More]

SQLBits 3 – The Movies

by Allan Mitchell 12 Nov 2008 13:32
Back in September we ad our community conference SQLBits and MS were there filming.  In addition to my presentation there are a load of other good ones.  The whole of the BI track was filmed.  I know this is off topic slightly but it well worth taking a look. So head over there and see what there is.

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]

Tips and Tricks in SSIS Presentation

by Allan Mitchell 1 Nov 2008 12:05
Here is a Live Meeting presentation I did for the Polish SQL Server User Group on Tips and Tricks I use when out and about using SSIS (It is in English).  It lasts about an hour and you can either watch it online or download the wmv to view later   Tips and Tricks in SSIS

More Value From Data Using Data Mining Presentation

by Allan Mitchell 1 Nov 2008 11:37
Here is a presentation I gave at the SQLBits conference in September which was recorded by Microsoft.  Usually I speak about SSIS but on this particular event I thought people would like to hear something different from me. Microsoft are making a big play for making Data Mining more accessible to everyone and not just boffins.  In this presentation I give an overview of data mining and then do some demonstrations using the excellent Excel Add-Ins available from Microsoft SQL Server 2008 SQL Server 2005 I hope you enjoy this presentation http://go.microso... [More]

SSAS Cache Warming Using SSIS

by Allan Mitchell 31 Oct 2008 08:22
Having your users walk into a cold cache on a morning is something that will cause them to have slower running queries initially.  Cache warming takes care of a lot of that by executing typical queries against the newly processed cube and thereby building the cache ready for your users when they get in.  There are a number of ways to warm the cache available. Chris Webb has already blogged about how he has done it and we thought we would show you a variation on that theme. UPDATE: The Trace File Source Adapteris now available. The package is relatively simple.     ... [More]

Escaping In Expressions

by Darren Green 30 Oct 2008 17:18
The expressions language is a C style syntax, so you may need to escape certain characters, for example: "C:\FolderPath\" + @VariableName Should be "C:\\FolderPath\\" + @VariableName Another use of the escape sequence allows you to specify character codes, like this \xNNNN, where NNNN is the Unicode character code that you want. For example the following expression will produce the same result as the previous example as the Unicode character code 005C equals a back slash character: "C:\x005CFolderPath\x005C" + @VariableName For more information about ... [More]

Building a Successful ETL Project

by Allan Mitchell 28 Oct 2008 09:00
When we are building ETL projects we need to do some planning up front to make sure we have all the things we need to make it a success. Here are some of the things we consider when embarking on an ETL Project. There are two lists. The first is around an ETL Project in general and the second is around things we think about specifically for SSIS. Review target data model Identify source systems (Owners, RDBMS types, Permissions) Analyze and profile source systems (usge patterns, Windows of opportunity) Document source data defects and anomalies (known issues, data profiling) ... [More]

Create MSDB Folders Through Code

by Darren Green 28 Oct 2008 08:58
You can create package folders through SSMS, but you may also wish to do this as part of a deployment process or installation. In this case you will want programmatic method for managing folders, so how can this be done? The short answer is, go and look at the table msdb.dbo. sysdtspackagefolders90. This where folder information is stored, using a simple parent and child hierarchy format. To add new folder directly we just insert into the table - INSERT INTO dbo.sysdtspackagefolders90 ( folderid ,parentfolderid ,foldername) VALUES ( NEWID() ... [More]

Redistribute SSIS

by Darren Green 28 Oct 2008 08:55
The DTS component of SQL Server 2000 was a redistributable client component. This mean anyone with a SQL Server Client Access Licence (CAL) could not only connect to SQL Server in the normal manner, but that could also run DTS packages on their machine. Since DTS and SSIS have no client server, or remote execution feature, this was rather useful. The DTS install was officially documented in redist.txt and expanded upon in articles like Redistributing DTS with your program . SSIS is not redistributable. If you want to run a SSIS package on a machine, you need to licence that machine with a ser... [More]

Logical OR in Control Flow to Skip Task

by Allan Mitchell 28 Oct 2008 08:52
Here is an example of how to do something that in DTS we would have had to do some coding in Script.  In our packages we often find the need to follow one path or another in the Control flow based on some condition that exists at runtime.  In the example below we use a variable value to decide what path to take.   Here is the control flow along with the variables pane. The icons on the workflow indicate we have expressions thereon. The expression on the workflow constraint between tasks 1 and 2 is shown here. As you can see we want the workflow to be followed if our variabl... [More]

Disabling tasks through Expressions

by Darren Green 28 Oct 2008 08:43
All Containers include a Disable property. This is what is used for the right-click Disable/Enable functionality you may have used when developing packages in the designer. Expressions are evaluated at run-time and can be used to set properties, so it follows that you could use an expression on the Disable property to control execution of the container. This will not always work. By design, the Disable property is evaluated quite early on during package execution. This may be before you have set a variable that is subsequently used within your Disable property expression. This may caused unex... [More]


by Darren Green 28 Oct 2008 08:40
Data Flow Property Expressions Property expressions within the data flow are not immediately obvious. Whilst most pipeline or data flow components do support them for their properties, the expression is not generally exposed through the component user interface, neither is the Expressions property shown properties grid for the component. Property expressions are only available at the task level, so for those components that do support expressions, you need to look in the parent data flow task's property Expressions collection. This can be accessed through the Property Grid for the task. You n... [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]


Comment RSS