This year I’ve delivered my full-day Building Better SSIS Packages course a half dozen times across the country, but I hadn’t scheduled this class in the Dallas area this year. As of today, that has changed! I’m happy to announce that I’ll be delivering this class at the Microsoft office in Las Colinas next month, on Friday, August 25th. Registration…
When designing the ETL architecture for new or changing systems, one of the more common needs is to create a process that copies all of the data from a set of tables (perhaps even all tables) from a source system to a destination database. This is especially prevalent when building an operation data store (ODS), or building a set of…
In the previous post in this series, I addressed how to use an on-premises instance of SSIS to move data to and from Azure databases. If you’re running in a pure Azure environment without on-prem SQL Server, that load architecture would present some challenges. However, by running SSIS in an Azure VM, you can communicate move data into or out…
If you are building SQL Server Integration Services (SSIS) packages, using the SSIS catalog as a deployment target is usually the easiest and most efficient solution. However, the SSIS catalog is not created by default, even when you select the SSIS components during the SQL Server installation process. The good news is that creating the SSIS catalog is a quick…
Among the new features of SSIS 2016, one of my favorite is incremental package deployment. This new functionality allows the deployment of a single package to an SSIS catalog without having to deploy the entire project. Since the release of this version, I’ve been asked the following question a few times: “How does one go about deleting a package from…
This summer, I will be delivering my popular full-day training class Building Better SSIS Packages in three different cities across the southern United States: Thursday, June 1st: Pensacola, Florida Friday, June 16th: Pasadena, Texas (just outside of Houston) Friday, July 14th: Lawrenceville, Georgia (near Atlanta) This course is designed for those with some previous exposure to or experience with SQL…
Making the most of the SSIS catalog requires an understanding of how to access the information stored in the logging tables. Although there are built-in reports to show this information, there are limitations in their use. Fortunately, the logging tables in the SSIS catalog database are (mostly) straightforward and easy to understand once you’ve worked with them a bit. In…
In my introduction to the topic of using SSIS in the cloud, I noted that SSIS is not currently available in a platform as a service (PaaS). Unlike SQL server databases and more recently SSAS, which are both available as both on-prem and PaaS services, Integration Services must be run on a user-installed instance of SQL Server. This limitation means…
The FTP protocol is one of the oldest methods for sharing and moving files. Although frequently considered to be an “old-school” way to transfer data, FTP is still relied upon in most every data movement architecture. Sadly, the functionality around FTP is very limited in SQL Server Integration Services. A common project requirement is to retrieve a list of files from…
In my ongoing series on ETL best practices, I recently wrote about the importance of logging in extract-transform-load processes. For users of later versions (2012 and beyond) of SQL Server Integration Services, adding logging to those ETL processes is very simple. Since logging is managed by the catalog, adding logging to an ETL process is a one-step process and requires…