A relatively common requirement in ETL processing is to break records into disparate outputs based on an alphabetical split on a range of letters. A practical example of this would be a work queue for collections staff based on last name; records would be pulled from a common source and then separated into multiple outputs based on a the Customer…
So the question came up earlier today about the RIGHT() and LEFT() functions in the SSIS expression language. Like the Transact-SQL functions, one might assume that these functions would exist in SSIS expression language to snatch a specified subset of a string. That assumption would be only half right. Don’t go digging for a LEFT() function in the expression language,…
So you’ve got some packages that regularly extract data to one or more text files, but you know that from time to time some of the queries will not return any data. However, you find in SSIS that, in a flat file export package, the output file is created regardless of whether any rows are written to the file, and…
It has been well-documented through myriad blogs and forum posts about the case sensitivity of the comparisons in the SSIS lookup transformation (a good review can be found here). In a nutshell, a comparison using the lookup transformation is case sensitive when using the default setting of Full Cache, even if the values in the database are stored in a case…
Recently, my friend Jack Corbett asked a question online: In a nutshell, the SSIS foreach loop will enumerate a given list of items (files in a directory, nodes in an XML file, static list of values, etc.) and will perform some operation for each of the items in the collection. This behavior is similar to foreach loop constructs that are…
Working with an ETL design that does a straight insert operation is the most simple, but often ETL processes are required to update data as well. There are several ways to go about updating data with SSIS, one of which (the staging table method) requires a bit more setup but can also perform significantly better. Updating Data with SSIS I…
I ran into a situation this week that brought to light a subtle syntactical error I’d made in creating an SSIS package. I’ve got a client that has given me access to their development server to create some complex extraction queries, which will eventually be rolled into SSIS packages. Since I’m working with read-only access and cannot create stored procedures…
For SSIS developers, the need for proper documentation is crucial. However, the built-in object for documentation, the annotation, is difficult to use. It doesn’t wrap text, doesn’t support varying font styles in a single instance, and doesn’t offer spell checking. Further, all annotations are “at large” and are not attached to a particular object – they are associated with a…
The SSIS expression language is a powerful yet enigmatic entity. Once you get used to its syntax – which is part C#, part T-SQL and part *WTH?!?* – it’s actually somewhat fun to use. However, one thing it appears to be lacking is the ability to use an if/then/else statement. However, there actually is such an instrument, though it doesn’t…
Having cut my teeth on SQL Server 2000, I had some (but not a lot) of DTS experience. Recently I found myself presented with a massive ETL project, and found a perfect opportunity to get started with SQL Server Integration Services. With the help of a couple of great books and one magazine (I’ll mention those shortly), I have spent…