SQLServerCentral.com Articles tagged ETL

Dynamic ETL with SSIS

Fri, 03 May 2013 06:00:00 UT

Learn how to dynamically load data from ETL load files using SSIS as a shell. The code downloads files from FTP, parses them and loads them into the database.

T-SQL insert using SSIS Data Pump

Tue, 05 Feb 2013 07:00:00 UT

This article describes a T-SQL procedure to insert data using the SSIS Data Pump

We Loaded 1TB in 30 Minutes with SSIS, and So Can You

Fri, 25 Jan 2013 07:00:00 UT

In February 2008, Microsoft announced a record-breaking data load using Microsoft® SQL Server® Integration Services (SSIS): 1 TB of data in less than 30 minutes. That data load, using SQL Server Integration Services, was 30% faster than the previous best time using a commercial ETL tool. This paper outlines what it took: the software, hardware, and configuration used. We will describe what we did to achieve that result, and offer suggestions for how to relate these techniques to typical scenarios. Even for customers who don't have needs quite like this benchmark, such efforts can teach a lot about getting optimal performance.

Merge, Metadata and the Data Mart ETL

Thu, 27 Dec 2012 07:00:00 UT

Here's a great method for managing ETL for data marts and small data warehouses with just T-SQL.

Importing Data From Excel Using SSIS - Part 1

Mon, 08 Oct 2012 06:00:00 UT

Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it?

Self Scheduling Jobs

Wed, 20 Jun 2012 06:00:00 UT

Jobs with a calcuable run date can self schedule themselves.

ETL Performance Auditing - Part 3: Auditing Data Warehouse Phase

Tue, 19 Jun 2012 06:00:00 UT

Easy way to setup a comprehensive ETL Performance auditing Solution no matter how complicated your ETL setup.

ETL Performance Auditing - Part 2: Auditing Data Staging Phase

Tue, 12 Jun 2012 06:00:00 UT

Easy way to setup a comprehensive ETL Performance auditing Solution no matter how complicated your ETL setup.

Serving Warm SSIS Errors

Thu, 12 Apr 2012 06:00:00 UT

Delivering well formated SSIS Package errors through Emails.

How to automate your .NET and SQL Server deployments
Deploy .NET code and SQL Server databases in a single repeatable process with Red Gate Deployment Manager. Start deploying with a 28-day trial

Solving a Problem Importing Integers Using Format Files

Thu, 16 Feb 2012 07:00:00 UT

When using format files to import data into SQL Server, the Integer(INT) data type needs special handling.

Process Support Database Framework

Thu, 26 Jan 2012 07:00:00 UT

Do you use or need a database process framework? Read on to see if this is something that might help you build better database software processes.

Deleting Rows in Incremental Loads – Level 5 of the Stairway to Integration Services

Fri, 04 Nov 2011 06:00:00 UT

In this installment of the Stairway to Integration Services, MVP Andy Leonard completes the incremental load section with a look at how deletes at the source are handled when loading data into SQL Server.

Database source control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Get started now.

Extract multiple files from multiple directories with a data flow task

Thu, 27 Oct 2011 06:00:00 UT

This article shows how to extract multiple files from multiple sub-directories into a destination table using a single Data Flow Task created by the Import/Export wizard.

Extract multiple files with a MultiFlatFileConnection data flow task

Thu, 20 Oct 2011 06:00:00 UT

A data flow task generated by the SSIS Import and Export Wizard can be configured to extract data from multiple files by changing the default connection manager to a MultiFlatFileConnection.

Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services

Wed, 12 Oct 2011 06:00:00 UT

Part four of this stairway series examines the process of updating the data in an existing table in a load. MVP Andy Leonard talks about the way in which packages can handle changes in a repeatable fashion.

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

Managing Data Removal Using SSIS

Tue, 13 Sep 2011 06:00:00 UT

Managing Data removal During ETL. First of Comprehensive tools designed for ease of use especially in enterprise Projects.

Incremental Data Loading Using CDC

Mon, 17 Jan 2011 07:00:00 UT

Describes a design pattern for using CDC to power fast and efficient incremental data loads.

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

Creating Inferred Dimension Members with SSIS

Wed, 08 Dec 2010 07:00:00 UT

This article will show you how to use SSIS to create inferred dimension members on the fly during a fact table load process.

Creating Dynamic Outputs in SSIS to an SQL Database

Wed, 10 Nov 2010 07:00:00 UT

This article shows how you can dynamically split data based on transaction type to its own destination table using Integration Services.

Dynamic ETL with SSIS

Fri, 03 May 2013 06:00:00 UT

Learn how to dynamically load data from ETL load files using SSIS as a shell. The code downloads files from FTP, parses them and loads them into the database.

Data Distribution - Part 2

Thu, 14 Oct 2010 06:00:00 UT

In the second part of this series, Leo Peysakhovich provides a mechanism for tracking real time data changes.

SSIS: Conditional File Processing in a ForEach Loop

Thu, 26 Aug 2010 06:00:00 UT

I’ve fielded a number of requests recently asking how to interrogate a file within SSIS and change the processing rules...

Going From Many Schemas To One Schema

Mon, 09 Aug 2010 06:00:00 UT

A Technique to deal with moving data from multiple schemas into one table

Is it Time to Switch to ELT?

Wed, 14 Jul 2010 06:00:00 UT

Extract-load-transform integration can offer performance and cost advantages over ETL. Here's how to pick the right approach.

Data Warehousing Architecture - Designing the Data Staging Area

Wed, 07 Jul 2010 06:00:00 UT

The staging area tends to be one of the more overlooked components of a data warehouse architecture, and yet it is an integral part of the ETL component design. Learn why it is best to design the staging layer right the first time, enabling support of various ETL processes and related methodology, recoverability and scalability.

Speeding Up Incremental ETL Processes in SSIS by Using MD5 Hashes

Tue, 08 Jun 2010 06:00:00 UT

Speeding up incremental ETL processes in SSIS by tracking changes to records in an MD5 Hash

SSIS for Multiple Environments

Tue, 27 Apr 2010 06:00:00 UT

In this article, Zach Mattson shows us how you can set up SSIS to handle multiple application environments and easily move packages from development to QA to production.

Kimball University: Three ETL Compromises to Avoid

Thu, 11 Mar 2010 07:00:00 UT

Why neglecting slowly changing dimensions, failing to capture metadata and overlooking scope creep can be the undoing of a dimensional data warehousing initiative.

Import Excel Spreadsheet to Database Tables

Wed, 17 Feb 2010 07:00:00 UT

Importing Excel Spreadsheets is something that we often do with SQL Server. However it's not as easy as you would like as we move to the 64 bit platform. Peter He brings us a method that will work on all platforms.

Fast normalization of a denormalized data source, using surrogate IDs

Mon, 25 Jan 2010 07:00:00 UT

A cursor-free way of normalizing data from a denormalized data source into a database which uses "surrogate" IDs.