ETL

Security Testing with extreme data volume ranges

June 19, 2020 by Timothy Smith

When we develop security testing within inconsistent data volume situations, we should consider our use of anti-malware applications that use behavioral analysis. Many of these applications are designed to catch and flag unusual behavior. This may help prevent attacks, but it may also cause ETL flows to be disrupted, potentially disrupting our customers or clients. While we may have a consistent flow of data throughout a time period – allowing for a normal window of behavior to occur – we may also have an inconsistent data schedule or inconsistent amount of data that cause these applications to flag files, directories, or the process itself.

Lock Configurations with SQL Bulk Insert

May 11, 2020 by Timothy Smith

One challenge we may face when using SQL bulk insert is whether we want to allow access during the operation or prevent access and how we coordinate this with possible following transactions. We’ll look at working with a few configurations of this tool and how we may apply them in OLAP, OLPT, and mixed environments where we may want to use the tool’s flexibility for our data import needs.

An overview of ETL and ELT architecture

April 21, 2020 by Aveek Das

This article explains what the basic features and differences between ETL and ELT are. I’m also going to explain in detail what an ELT pipeline is and a relevant architecture for the same in Azure. So far, we have come a long way dealing with ETL tools which basically are Extract, Transformation and Load technique used in populating a data warehouse. ELT, on the other hand, is another way to load data into a warehouse that implements the process of Extract, Load and Transform.

An overview of the DTExec utility in SSIS

April 2, 2020 by Hadi Fadlallah

DTExec is a tool used to configure and execute SQL Server Integration Services (SSIS) packages. Many developers and database administrators use this tool, especially when automating the package execution using third-party applications or non-supported programming languages.

Biml alternatives: Building SSIS packages programmatically using EzAPI

March 26, 2020 by Hadi Fadlallah

In the previously published article, Biml alternatives: Building SSIS packages programmatically using ManagedDTS, we talked about building SSIS packages using the managed object model of the SSIS engine (ManagedDTS). In this article, we will illustrate another Biml alternative, which is the EzApi class library, and we will make a comparison between both technologies.

Biml alternatives: Building SSIS packages programmatically using ManagedDTS

March 25, 2020 by Hadi Fadlallah

In the previously published articles in this series, we have explained how to use Biml to create and manage SQL Server Integration Services (SSIS) packages. In this article, we will talk about the first alternative of this markup language which is the Integration Services managed object model (ManagedDTS and related assemblies) provided by Microsoft.

In this article, we will first illustrate how to create, save and execute SSIS packages using ManagedDTS in C#, then we will do a small comparison with Biml.

An overview of the SSIS FTP Task

March 18, 2020 by Hadi Fadlallah

In this article, we will explain the File Transfer Protocol (FTP), and we will give an overview of the FTP task in SQL Server Integration Services (SSIS) and FTP connection manager.

Using external C# script files within Biml

March 17, 2020 by Hadi Fadlallah

In the previously published article, Extending Biml with C# scripts, we have explained how to use VB or C# scripts within the Biml code to prevent doing repetitive development work. But in this solution, the C# scripts and classes are only available within a single file while we may need to use them in many.

Extending Biml with C# scripts

March 16, 2020 by Hadi Fadlallah

In our previously published articles in the Biml series, we have explained what Biml is, and how to use this language to generate SQL Server Integration Services (SSIS) packages. In this article, we will explain BimlScript, which is an extension of this markup language with VB or C# scripts.

Using Biml scripts to generate SSIS packages

March 13, 2020 by Hadi Fadlallah

In the previous article, Converting SSIS packages to Biml scripts, we explained how to convert existing SSIS packages into Biml scripts using Import Packages tool and we mentioned that this could be an efficient way to learn this markup language since it lets the user compare between the well-known SSIS objects found in the package and the correspondent elements in the generated scripts.

Converting SSIS packages to Biml scripts

March 13, 2020 by Hadi Fadlallah

In our previous article, Getting started with Biml, we have explained what Biml is, what are the related tools and resources and how to get started with this technology. In this article, we will explain how to generate scripts from existing SSIS packages by showing all related options. Then, we will analyze the generated script and identify how each object is mentioned in the script.

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

March 4, 2020 by Hadi Fadlallah

T-SQL (Transact-SQL) is a set of SQL language programming extensions developed by Sybase and Microsoft. These extensions are adopted in Microsoft SQL Server and it provides a powerful set of functions to execute analytics and administrative commands; also it is very helpful during data wrangling.

Getting started with Biml

February 26, 2020 by Hadi Fadlallah

In this article, we will give a brief overview of BIML by showing how it can be installed and how we can use it to generate SSIS packages using the BimlExpress tool. Finally, we will provide some good resources to learn this technology.

Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow

February 14, 2020 by Sifiso W. Ndlovu

The Data Flow Task is an essential component in SQL Server Integration Services (SSIS) as it provides SSIS ETL developers with an ability to conveniently extract data from various data sources; perform basic, fuzzy to advance data transformations; and migrate data into all kinds of data repository systems. Yet, with all its popularity and convenience, there are instances whereby the Data Flow Task is simply not good enough and recently, I got to experience such inefficiencies. To demonstrate some of the limitations of SSIS’s Data Flow Task, I have put together a random list of Premier League’s leading goal scorers for the 2019-2020 season.

Import data from multiple Excel files into SQL Server tables using an SSIS package

December 13, 2019 by Rajendra Gupta

This article explores an SSIS package for importing multiple Excel files data into SQL Server tables.

Sequence Containers in SSIS packages

December 6, 2019 by Rajendra Gupta

This article explores the Sequence container in SSIS package with examples.

SSIS Foreach Loop vs For Loop Container

December 4, 2019 by Hadi Fadlallah

In this article, first, we will briefly describe foreach loops and for loops. Then, we will give an overview of SSIS Foreach Loop Container and For Loop Container. Finally, we will compare these two containers.

Overview of SSIS Precedence Constraints

December 3, 2019 by Rajendra Gupta

This article explores the SSIS Precedence Constraints, along with examples and scenarios.

An overview of SSIS Pivot and SSIS Unpivot Transformations

November 28, 2019 by Rajendra Gupta

This article explores an SSIS Pivot transformation and SSIS Unpivot transformation for creating Pivot and Unpivot data tables.

SSIS Balanced Data Distributor Overview

November 22, 2019 by Hadi Fadlallah

In this article, we will give a brief overview of SSIS Balanced Data Distributor (BDD). In addition, we will give some examples and illustrate alternatives.

An overview of the Data Profiling task in SSIS

November 21, 2019 by Hadi Fadlallah

The Data Profiling task in SSIS is an important task that can be used to assess the quality of data sources. Unfortunately, this component is not widely used by many business intelligence developers.

SQL OFFSET FETCH Feature: Loading Large Volumes of Data Using Limited Resources with SSIS

November 14, 2019 by Hadi Fadlallah

In this article, we illustrate how to use the OFFSET FETCH feature as a solution for loading large volumes of data from a relational database using a machine with limited memory and preventing an out of memory exception. We describe how to load data in batches to avoid placing a large amount of data into memory.

SSIS Flat Files vs Raw Files

October 29, 2019 by Hadi Fadlallah

In this article, we will give an overview of using Flat Files and Raw Files in SSIS, then we will illustrate some of the differences between using these two types.

SSIS Script Component Overview

October 29, 2019 by Hadi Fadlallah

SSIS Script component is a prominent strength of SQL Server Integration Services since it allows developers to implement any complex logic and utilize libraries from the powerful .NET framework or third-parties.

SSIS XML Destination

October 11, 2019 by Hadi Fadlallah

Until now, Microsoft has not included the XML Destination component in SQL Server Integration Services (SSIS). Many years ago, this component was requested on the Microsoft connect website, but it was closed as “Won’t fix.” For this reason, many workarounds and third-party components were created. In this article, we’ll talk about these components and some of the popular solutions for exporting data to XML using SSIS.