For organizations using SQL Server 2012 and newer, the SSIS catalog is the ideal tool for storing, executing, and monitoring ETL logic. The SSIS catalog includes built-in reports that show execution activity for current and historical operations. While these built-in reports are very useful, they have one significant limitation: they can only be viewed from within SQL Server Management Studio….
Earlier this week, I blogged about the automatic cleanup process that that will clean up the SSIS catalog logging tables. This nightly process removes data for operations that are older than 365 days. While this is useful, many SSIS admins have complained that this process is very slow and contentious on large or busy SSISDB databases. In this post, I’ll…
Built into the SSIS catalog is a mechanism that can automatically purge log data after a set period of time. In this post, I’ll show you how to set up and manage that functionality. SSIS catalog automatic log cleanup The SQL Server Integration Services catalog database – SSISDB – has several dozen logging tables that are used to capture details…
When doing any new development or major overhaul of existing SSIS architecture, I almost always recommend to clients that they deploy those packages to the SSIS catalog. Using the catalog to store and execute SSIS packages takes a lot of the manual work out of development and maintenance, particularly when it comes to package logging. SSIS Catalog Execution Parameter Values…
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…
Temp tables are very handy when you have the need to store and manipulate an interim result set during ETL or other data processing operations. However, if you use SQL Server Integration Services as your ETL tool, you may find some challenges when trying to work with temp tables in SSIS packages, especially in the SSIS data flow. In this…
I am delighted to announce that I’ll be delivering my full-day course, Building Better SSIS Packages, at the SQLBits conference in London on Thursday, February 22nd. This course is aimed at data professionals with experience in SQL Server and a general understanding of SQL Server Integration Services concepts. This will be my second time to present at SQLBits, and my…
SQL Server Integration Services is one of the most popular extract-transform-load (ETL) tools on the market. Learning how to use SSIS to move and transform data is an essential skill for every data professional, whether you’re a DBA, SQL Server developer, data analyst, or architect. Classroom Training: Introduction to SSIS This 3-day hands-on course will teach you what you need…