Without a doubt, the most versatile data flow tool in the SSIS arsenal is the script component. This control grants ETL developers complete control over the flow of data, leverages the full power of the .NET Framework namespaces, and can be used as a source, destination, or transformation in data flow operations. Within the script logic, one can perform complex…
Through the course of my 8-someodd years of building and fixing ETL processes, I’ve had the opportunity to see a lot of ETL code. Some of that code was really good, well-thought-out and carefully executed. Other load processes were – well, let’s just say that they provide plenty of consulting opportunities (and I include much of my early code in…
In my most recent post in this series, I talked about how to use the SSIS object variable as an ADO recordset as a source in a data flow. By loading the result set of a query into this variable, the contents of the variable can be read by an SSIS script component and sent out through the SSIS pipeline….
Object variables in SSIS are incredibly versatile, allowing the storage of almost any type of data (even .NET objects). In my last post on this topic, I demonstrated how an SSIS object variable containing a .NET DataSet object could be used by the for each loop container as an iterator. In this post, I’ll continue the discussion by showing how…
Some time back I wrote about how to use the script component to parse out ragged data files in SSIS. In this post, I’ll continue the discussion to describe how to handle mixed format data files – specifically, those with several different record types in each file. In a perfect world, a flat file will contain a single record type….
The answer: It Depends. One of the more common problems I encounter when managing data quality, especially in an ETL process, is the proper handling of null, empty string, or zero values. When I put on my preaching shoes to talk about bad data, this is one the areas I have to spend a lot of time covering because it…
It’s an odd query, yes, but in preparation to write this post I actually typed the above phrase into my browser. No, I’m certainly not looking to burn down my house. In fact, wait here while I clear my search history, just in case. For the sake of argument, let’s say you’re planning to fry a turkey over the upcoming…
This is the first in a series of technical posts on using parent-child SSIS architecture. In this post, I will provide an overview of the architecture and describe the benefits of implementing a parent-child design pattern in SQL Server Integration Services structures. Parent-Child SSIS Architecture The simplest definition of SSIS parent-child architecture is that it consists of packages executing other…
I’m kicking off a new series of blog posts discussing the topic of parent-child architectures in SQL Server Integration Services. The links to the posts in this series are below. Parent-Child SSIS Architecture SSIS Parent-Child Architecture in Package Deployment Mode SSIS Parent-Child Architecture in Catalog Deployment Mode I still remember the first SSIS package I ever deployed to a production environment. …
When it comes to ETL, I’m a pessimist. Until proven otherwise, I assume that all data is bad, all connections are volatile, and all transformation logic is suspect. As such, I spent a lot of time addressing how to prepare for and handle errors in the ETL pipeline with SSIS. Building packages to expect and properly handle errors is a…