Integration Services (SSIS)

Character Map Transformations in SSIS packages

September 11, 2019 by Rajendra Gupta

This article explores the Character Map Transformation in SSIS package with available configurations.

Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types

September 9, 2019 by Hadi Fadlallah

In this article, I will give an overview of Execute SQL Task in SSIS and I will try to illustrate some of the differences between writing an expression to evaluate SqlStatementSource property or writing this expression within a variable and change the Execute SQL Task Source Type to a variable.

Using a CHECKPOINT in SSIS packages to restart package execution

September 5, 2019 by Rajendra Gupta

In the article, SQL Server CHECKPOINT, Lazy Writer, Eager Writer and Dirty Pages in SQL Server, we talked about the CHECKPOINT process for SQL Server databases. This article is about CHECKPOINT in SSIS package.

An overview of Error Handling in SSIS packages

September 5, 2019 by Rajendra Gupta

This article explains the process of configuring Error handling in SSIS package.

SSIS Conditional Split overview

September 4, 2019 by Dinesh Asanka

SQL Server Integration Services or SSIS is used as an ETL tool to extract-transform-load data from heterogeneous data sources to different databases. After extracting data from the different sources, most often there are a lot of transformations needed. One of the frequent transformations is SSIS Conditional Split.

Term Extraction Transformation in SSIS

September 2, 2019 by Rajendra Gupta

This article explores the Term extraction transformation in SSIS and its usage scenario.

SSIS OLE DB Destination vs SQL Server Destination

August 30, 2019 by Hadi Fadlallah

In this article, I will give an overview of SSIS OLE DB Destination and SQL Server Destination and I will try to illustrate some of the differences between both destination components based on my personal experience, SSIS official documentation and some other experts experience in this domain.

An Overview of the LOOKUP TRANSFORMATION in SSIS

August 29, 2019 by Rajendra Gupta

We will explore Lookup Transformation in SSIS in this article for incremental data loading in SQL Server.

SSIS Expression Tasks vs Evaluating variables as expressions

August 27, 2019 by Hadi Fadlallah

In this article, I will first give an introduction about SSIS expressions, then I will describe briefly the Expression Task and how to Evaluate a variable as expression. Then I will do a comparison between these two features to illustrate the similarities and differences between them.

Overview of SSIS Package Logging

August 23, 2019 by Rajendra Gupta

This article gives an overview of the different methods of SQL Server SSIS Package Logging.

SSIS Multicast Transformation overview

August 22, 2019 by Rajendra Gupta

This article explores the SSIS Multicast Transformation for creating different logical copies of source data.

SSIS OLE DB Source: SQL Command vs Table or View

August 22, 2019 by Hadi Fadlallah

Introduction

SQL Server Integration Services provides a wide variety of features that helps developers to build a robust Extract, Transform, and Load process. After many years contributing to SSIS-related tags on Stackoverflow.com, I can say that many developers have some misunderstandings about SSIS features (SSIS OLE DB Source, SSIS Expressions, SQL Server destination …) especially those which are very similar and have some common usability.

SSIS Conditional Split Transformation overview

August 16, 2019 by Rajendra Gupta

This article explores the SSIS Conditional Split Transform task to split data into multiple destinations based on the specified conditions.

An efficient approach to process a SSAS multidimensional OLAP cube

August 15, 2019 by Hadi Fadlallah

Introduction

While building and deploying an SSAS OLAP cube, there are two processing orders that you can choose from when you create a process operation:

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by Sifiso Ndlovu

Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.

SSIS Web Service Tasks

May 14, 2019 by Nikhilesh Patel

SQL server Integration services provides us with SSIS Web Service Tasks which assist us to drive web services.

SSIS interview questions

March 11, 2019 by Daniel Calbimonte

These SSIS interview questions cover many of the top topics that you will be asked when applying for your next SSIS developer job. Each question has links to articles that cover the topic in depth.

Importing Data into SQL Server from Compressed Files

March 5, 2019 by Rajendra Gupta

Introduction

I have seen many organizations receive data from various sources and import into SQL Server. You might receive data in various formats and want to import into SQL Server. We can prepare a ETL (Extract-Transform-Load) process to import data into the SQL Server. In doing so, might receive data in a compressed file, which helps to send data over the network using a ZIP file format because it reduces the file size significantly. If we are receiving a ZIP file to import into SQL Server, we need to unzip it and then only we can import data. We might need to create a ZIP file as well from the existing files.

SSIS Script component vs derived column

March 1, 2019 by Daniel Calbimonte

Introduction

In this article, we will show how to convert dates from dd/mm/yyyy to mm/dd/yyyy using the Script component and also derived columns in SSIS. We will also explain when to use a derived column (DC) and when to use the Script Component (SC).

Export SQL Server FILESTREAM Objects with PowerShell and SSIS

February 14, 2019 by Rajendra Gupta

In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.

How to work with SQL random numbers in SSIS

February 7, 2019 by Daniel Calbimonte

Introduction

In this article, we will show how to work with SQL random numbers in SSIS. To demonstrate this, we will have a table with people and we will create a winner randomly from that list.

Importing SQL Server FILESTREAM data with SSIS packages

February 1, 2019 by Rajendra Gupta

Initial configuration

We have been exploring the SQL Server FILESTREAM feature in this ongoing series of articles. In this previous article, Managing data with SQL Server FILESTREAM tables, we wrote about inserting FILESTREAM data into a FILESTREAM table and performing DML activities on it. Suppose we have created the FILESTREAM database in our instance and now we want to insert a large number of files into a FILESTREAM container. It is easy to write out the insert queries for a small number of files, but if the numbers of files were in huge quantity, it would be difficult to write out the code and insert data into it. It is difficult to manage such kind of requests regularly in the environment.

How to configure OData SSIS Connection for SharePoint Online

April 13, 2018 by Sifiso Ndlovu

As data warehouse developers, we often have to extract data from a myriad of source systems. Thus, whilst some source systems readily integrate with our ETL tools there are instances whereby we need to install additional drivers and software addons in order to successfully connect and extract data from other source systems. Microsoft SharePoint Online is one such source system that I recently had to extract data from and its connectors are by default not part of the standard SQL Server Integration Services (SSIS) package template. As SSIS developers we often don’t have solid background on environments such as SharePoint, thus figuring out which version of SharePoint Software Development Kit to install in order to enable successful connection from SSIS can sometimes be a frustrating experience. In this article, I try to alleviate some of that frustration by sharing some of my recent experiences relating to getting data out of a SharePoint list using SSIS.

Managing A Slowly Changing Dimension in SQL Server Integration Services

October 10, 2017 by Thomas LeBlanc

A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.

Using SSIS ForEach Loop containers to process files in Date Order

August 3, 2017 by Sifiso Ndlovu

One positive thing to come out of my recent project that involved rewriting one of the Data Marts from our Data Warehouse environment was a confirmation of my suspicions with regards to the behavior of SQL Server Integration Services’ (SSIS) ForEach Loop Container. You see, I have long suspected that the ForEach File Enumerator type in SSIS’s ForEach Loop Container does not process time stamped text files in an order that could be deemed correct to the human eye. For instance, Figure 1 shows a list of text files containing data relating to Marital Statuses of FIFA 2016 Ballon D’Or nominees.