Archive

Posts Tagged ‘#SSIS’

Why Not to Use Sort and Aggregate Transformations in SSIS

I often come across queries about the SSIS package optimization techniques. There are many, and all depends up on scenarios. However, there are a few fundamental principals to architect SSIS packages. These help us to design a package, which runs faster. I am going to tell about two transformations that I always avoid: Sort Transformation and Aggregate Transformation.

You can categorize all the transformation in two broader categories: Synchronous and Asynchronous. Or, you may divide them into three types: Non-Blocking, Semi-Blocking and Blocking. It’s not right place to discuss all Categories/Types here. I will be discussing only Blocking Transformations here. Just for the sake of your knowledge, the Non-Blocking Transformation belong to Synchronous category, while Semi-Blocking and Blocking belong to Asynchronous category.

The Blocking transformations accept entire input at first i.e. all rows from source, perform the assigned operation, and then give the output of the operation. During this process, you have to wait for the transformation to collect entire required rows before getting output/desired result. This is where it bring degradation in the Data Flow performance, and in turn, slow package.

Both the Sort and Aggregate transformations belong to the Blocking types. This is the reason I always recommend not to use them altogether in your pipeline. Then question arises: How to sort the rows and, if required, to get aggregate?

There are ways! In case of OLE DB Source and SQL Server as source, you need to use SQL query, not the Table Name from drop down list, and perform these operations in the query. These operations will run at the database engine machine and will give you output instantly. In case of flat file/raw file, request your team to calculate these values and put it either in anther file or in first one/two lines into the source file. I am sure you know how to read/escape initial lines while reading flat files. There is an option available. Check it out! This is how you can avoid using Blocking transformations like Sort and Aggregate and, in turn, achieve better package performance.

I will be blogging about other techniques to optimize the SSIS package. So, stay tuned!

 

Advertisements

SQL Server 2012 Business Intelligence Session at Ernst & Young, Bengaluru, 04 – 08 January, 2016

Finished 5-days Microsoft SQL Server BI session at Ernst & Young, Bengaluru…Nice participants…good learners…I enjoyed your company guys…

20160108_164837

Using OLE DB Data Source in SSIS

A data source in SSIS package is something which helps us to connect to the source of data like RDBMS, CSV File, Excel File, etc. and pull the data out.

A data source is used when we’re using Data Flow task. I’m sure you know about the Data Flow task, which is widely used, almost in every package. 🙂

There are various data sources such as ADO.NET, OLE DB, Flat File, Excel File, Raw File, etc. We choose a data source based on our requirement, from where we’re going to pull the data out.

Today, we’ll be talking about OLE DB Source, which can connect to almost all kinds of existing data sources, whether it’s SQL Server or Oracle or SAP NetWeaver or Access, etc. OLE DB uses the Connection Managers behind the scene in order to connect to a data source. It’s not Data Source but a Connection Manager decides what kind of driver you’re going to use and what database or data source you’re connecting to.

Read more…

Tags: ,

How to Export Data into Different Spreadsheet in Same Excel File in SSIS

We’re going to discuss how to export data into different spreadsheet in the same Excel file over here. We’re going to use SQL Server Integration Services 2012 and AdventureWorks2012 database. However, if you’re using earlier version like SSIS 2008 R2, It’ll be working perfectly fine. So, no worries!

I assume that you know how to create a package, what is Data Source, Data Destinations, etc. in SSIS.

Create a package and drag and drop a Data Flow task on Control Flow tab.

Step1

Read more…

How to get back SSIS Toolbox in Visual Studio 2012

I got confused when I tried SSIS 2012 for the very first time. In earlier versions of SSIS i.e. 2008 or 2008 R2, we have only a single Toolbox window in the package designer. While, the SSIS 2012 provides all tools such as Data Source, Transformations, Data Destinations, etc. in a new windows called SSIS Toolbox. I’m unsure why the SQL Serve team has done this and what they wanted to achieve by this. I guess they thought it would be easier for a package developer to have all tools and controls in a separate toolbox window from management point of view. To me, that’s okay..:)

So, if you are not getting SSIS Toolbox window in your Visual Studio, where to find it out?

What all you need to do is go to View -> Other Windows -> SSIS Toolbox. Click on it and it will appear to the left side of Visual Studio by default. You may dock it anywhere, if you wish. Don’t get confused when you see empty original Toolbox window over there as well. It doesn’t contain any SSIS tools.

Take a look at image below…

SSISToolbox

Trust it helps you out.

SSIS Training at Mphasis, Bagmane Tech Park, Bangalore, Jan 2 – Jan 5, 2014

Started new year with SSIS training at Mphasis, Bagmane Tech Park, Bangalore on Jan 2nd. Just done with it. Great learning and sharing during session…Nice participants…

IMAG0798

SSIS 2012 Training at NextEdge Software Solutions, Bangalore, May 28th – May 30th, 2013

SSIS 2012 training comes to an end at NextEdge Software Solutions, Bangalore. Nice and jolly participants..enjoyed a lot…

WP_20130530_004