Yesterday I wrote about the little-known but still useful multiple flat file connection manager. In this post, I will briefly show a more commonly used alternative approach for processing multiple data files: the foreach loop container. The SSIS foreach loop container The foreach loop container is used to iterate through a discrete list of items at runtime. That list could…
When building an ETL pipeline to import data from a text file, it’s very common to have the incoming data spread across multiple files. For example, if you are ingesting files generated on a periodic basis (per day, per hour, etc.), you could have dozens or hundreds of files with identical structure. This is an ideal setup for building a…
When extracting data from a flat file, it is handy to have the name of the file from which the data was retrieved. Whether you capture this information for auditing purposes, or you include it directly in the output table, the odds are good that you’ll want to have that filename for use later in the process. You could do…
I’ve been writing about change tracking in SQL Server for some time now, but I recently came across a permissions issue on a change tracking table that I’d never encountered before. When attempting to insert data into a change tracking-enabled table using an account with no select permissions, I learned that the effective security principal must have both insert and…
Call me pedantic, but I can be a stickler for standardized naming conventions. Whether it’s application code, database objects, documentation, or other digital assets, I find a tremendous amount of value in establishing a set pattern for naming and formatting. Especially for organizations with a large digital landscape, having a documented and agreed-upon standard for object names can make developers…
It’s that time again! My Eleven Days of Festivus blogging extravaganza is now in its third year, and I’m happy to share one blog post per day with you for the 11 days between now and Festivus Eve (December 22nd). As before, I’ll link all of the posts from this page. May your feats of strength be mighty, your airing…
It’s keynote time again! The second full day of the conference starts now, and like yesterday, I’ll be live-blogging during the keynote announcements. Our community Wendy Pastrick starts us off by sharing a bit of the softer side of the Summit – some of the stories, the networking opportunities, and the relationships built around this now 20-year-old event. We learn…
Today is the first full day of the PASS Summit in Seattle, Washington. This is the 20th year of the Summit, which brings together several thousand data professionals for a week of learning and networking. This morning, I will be live-blogging the keynote. This post will be updated periodically through the course of the keynote. If you are not in…
It has been almost impossible to avoid reading about the numerous large-scale data breaches reported on a seemingly daily basis. Stories of bad actors getting their hands on personal data are terrifying and always result in bad press for the breached company. However, not all data exposure scenarios make the news, and many can go unnoticed or unreported for years. Data leaks…
I am honored to have been selected to be a presenter at this year’s PASS Summit coming up this November. I will be presenting a half-day talk entitled Build a Metadata-Driven ETL Repository with Biml and SSIS: Your boss tells you that a new field will be added to one of your vendor’s data files, and asks how long it will…