SSIS

Upcoming SQL Saturday Precons

I’m happy to announce that I’ll be delivering three, one-day preconference seminars this summer prior to three different SQL Saturday events: Iowa City, Iowa – Friday, July 26th (before SQL Saturday 239 – East Iowa) Orlando, Florida – Friday, September 13th (before SQL Saturday 232 – Orlando) Denver, Colorado – Friday, September 27th (before SQL Saturday 190 – Denver) For…


Using the SSIS Object Variable as a Result Set Enumerator

In the first post in this series, I covered the basics of object typed variables in SQL Server Integration Services, along with a brief examination of some potential use cases.  In this installment, I’m going to illustrate the most common use of object typed variables in SSIS: using an object variable as an ADO recordset within a loop container to…


Business Intelligence Projects now supported in Visual Studio 2012

Since the release of Visual Studio 2012, business intelligence developers have been limited in how much they could use this tool due to the fact that it did not support BI project types (SSIS, SSAS, and SSRS).  Today, that limitation is now gone with the release by Microsoft of SQL Server Data Tools – Business Intelligence for Visual Studio 2012. …


Using Object Typed Variables in SSIS

Note: This will be the first post in a short series on using object typed variables (also known as SSIS object variables) in SQL Server Integration Services. When defining variables in SSIS, the ETL developer has several data type options to choose from depending on the information to be stored in each variable.  Included in the options are String, Boolean,…


Ragged Flat File Processing in SSIS

Frequently I am asked how to properly use SQL Server Integration Services to handle ragged flat files, an example of which is shown below.  In this small sample file, you can see that we are dealing with medical data with an inconsistent format.  As shown, the rows in the pipe-delimited file can have as few as seven and as many…


“It’s Alive” – SSIS Design Patterns

I’m happy to announce that the book I’ve been working on for the past two years, SSIS Design Patterns, is complete and has been released for sale as of today. I got the unique privilege to work alongside SSIS rock stars Andy Leonard, Jessica Moss, Matt Masson, and Michelle Ufford to scribe what will hopefully be a must-have book for…


SSIS: Conditional File Processing in a ForEach Loop

I’ve fielded a number of requests recently asking how to interrogate a file within SSIS and change the processing rules based on the metadata of said file. A recent forum poster specifically asked about using the foreach loop to iterate through the files in a directory and, on a per-file basis, either process the file or skip the file if…


Book Review – SQL Server Integration Services: Problem – Design – Solution

I usually don’t do book reviews (at least publicly, anyway), but when I find a piece of work that I really get a lot out of, I don’t mind sharing my experience. Such was the case with a book I finished recently. SQL Server 2008 Integration Services: Problem – Design – Solution is a concise guide to becoming a better…


An Un-CATCHable Error?

I’ve been using the scripting tools in SSIS for some time, but I came across something today that I can’t quite explain.  I normally don’t posts unresolved problems on my blog, but I’m trying out a strategy suggested by my friend Lee Everest by sharing unfinished work in the hopes that my research and troubleshooting can help someone else. So…


Alpha Split in SSIS, Redux

So I’ve discovered another benefit of being a technical blogger.  Not only do you get some kudos when you write something that helps someone else, but if you offer up a less-than-optimal solution, you’ll get some suggestions on how it can be done better.  I’ve had my share of the former, but earlier this week I experienced the latter. Last…