Tim Mitchell
Follow Tim Mitchell on Twitter  Like Tim Mitchell on Facebook  Subscribe on YouTube  Connect on LinkedIn  Subscribe to the Data Geek Newsletter

SSIS

How much memory does SSIS need

How Much Memory Does SSIS need?

One of the most common questions I am asked about ETL configuration is how much memory SSIS requires. When configuring a new instance of SQL Server running SSIS, or adding SSIS to an existing SQL Server installation, it is important to understand how SSIS uses memory, and what factors in your package design will impact the amount of memory used…


A Better Way to Clean Up the SSIS Catalog Database

Earlier this week, I blogged about the automatic cleanup process that purges old data from 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…


delete

SSIS Catalog Automatic Log Cleanup

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…


SSIS Catalog Execution Parameter Values

SSIS Catalog Execution Parameter Values

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. When you execute a package…


Processing Multiple Files in SSIS with the Foreach Loop

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…


MULTIFLATFILE

Using the SSIS Multiple Flat Files Connection Manager

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…


FileNameColumnName

Extract the File Name in SSIS Data Flows using the FileNameColumnName Property

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 in SSIS

Temp Tables in SSIS

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…


Training Day in London: Building Better SSIS Packages

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…


PASS Summit 2017

PASS Summit 2017 Day 2

It’s day 2 of the PASS Summit. Yesterday was a whirlwind of a day, after my early-morning post-Halloween trip to Seattle. I spent some time with my Microsoft friends in the SQL Clinic and visited several vendor receptions, followed by a quick visit to the karaoke party. Today is the Cosmos DB keynote by Rimma Nehme. I’ve heard Dr. Nehme…