What is permissible is not always honorable. – Marcus Tullius Cicero Rules. Best practices. Guidelines. Design patterns. Policies. All are good and necessary, and you’ll rarely find anyone who will argue against the need to establish boundaries and set expectations. But can adherence to rules and design patterns be taken too far? I believe it can. The Rules Gone Amok:…
“Learning to trust is one of life’s most difficult tasks.” – Isaac Watts As data professionals, there are times when our jobs are relatively easy. Back up the databases. Create the dashboard report. Move the data from flat files to the database. Create documentation. There are lots of cogs in those machines, but an experienced technologist will have little trouble…
If you’ve used SSIS for any significant amount of time, you’ve almost certainly run into this roadblock: You need to download a file stored on an SFTP (secure FTP) server, but connecting to SFTP with SSIS is not natively supported. There is a built-in FTP task in Integration Services, but it is very limited in its functionality and will not…
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…
Earlier this week, I confirmed two full-day seminars I’ll be delivering in conjunction with SQL Saturday events this fall. I’ve got a brand new course entitled “Building Better SSIS Packages” which I’ll be delivering in Denver and Boston. Here’s a brief into to this course: SQL Server Integration Services is deceptively easy to use. With a few hours of instruction…
The key to successful analysis is to ask enough good questions. How do you know that you’ve asked enough questions? When you think you have all the information you need to get started, ask 10 more good questions. Those ten questions will lead to five more, which will lead to 3 more, and so on. After you get started, if…
I’m a fixer. A tinkerer. As a kid, I would take my toys apart just to see how they worked, and to prove that I could put them back together again (and these experiments often had mixed results). Even today, I’m still kind of a shade tree handyman. In fact, earlier this week I had to fix a broken safety…
During data load or transformation processes, capturing a distinct row number for incoming data can be beneficial for the ETL process itself, as well as for use in the destination database. Having an arbitrary, incrementing row number assigned to each row can help to determine the order in which the rows of data were processed, and can provide a unique…
Now that the details have been finalized, I can announce that a new SSIS class coming up this fall. My Linchpin People cohort Andy Leonard and I will be delivering our Advanced SSIS training class in the Washington, DC area in December. This class is four days in length, ideally suited for experienced SSIS developers who want to take their skills…
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…