Archive

Posts Tagged ‘#SSIS #SSISPackage’

OLE DB versus ADO NET Components in SSIS

ETL Package developers often ignore this question: What is the difference between OLE DB and ADO NET components? Should I use OLE DB Source/Destination or ADO NET Source/Destination? Which one is faster?

Let me explain what the difference is. The only difference is both of them use drivers/providers behind the scene, which have been developed using different technologies. ADO NET providers use ADO.NET along with C# behind the scene, while OLE DB providers use something else. Recently, the Microsoft said that they would not be enhancing the features in OLE DB providers. So, don’t expect more with OLE DB. Hence, you need to use ADO NET providers more and more. Don’t get scared! In fact, if you wish to connect to Azure SQL Database, you need to use ADO NET providers, as the OLE DB doesn’t offer flexibility to connect to Azure SQL Database or Data Warehouse. However, the OLE DB has got more providers to connect to different types of data stores compare to the ADO NET, at least as of now.

As far as performance is concerned, I personally did R&D with both the Sources/Destinations with huge data i.e. 1-5 GB, Database to Database, Database to File, File to Database, all types of package. Eventually, the statistics say that OLE DB has got an edge over ADO NET, and give you 60% more performance than that of ADO NET. Yes! ADO NET Sources/Destinations process data slower than OLE DB. Here, I would like to mention that I tweaked all option like DefaultBufferMaxRows, DefaultBufferSize, AutoAdjustBufferSize for SSIS 2016, etc. and then reached to this conclusion. And my experience of package development corroborates with this.

Hence, I recommend using OLE DB Source/Destination for the time being, until Microsoft enhances the ADO NET providers. Yes! You may need to use ADO NET in case of Azure, which is becoming very common now-a-days as every client is moving databases to Azure.

There are many other factors which affect the package performance, which I am going to discuss in other blogs. So, stay tuned!

Hope this helps you out.

 

Advertisements

Data Sources, Data Transformations and Data Destinations in SSIS 2012

I’ve always come across with pretty common questions while dealing with SSIS training sessions – What are Data Sources, Data Transformations and Data Destinations in SSIS package? Why do we use them? Where do we use them? Do we use them all the time? blah…blah…

So, let’s discuss a little bit about them.

I’m sure every package developer comes across requirements where we need to import/export data from one data source to another data source. And, the very basic control/tool that SSIS provides with us is Data Flow task. I assume we are aware of why we use Data Flow task. Yet, let me briefly explain it.

The Data Flow task is a tool which is used to define activities involved into import/export data from one source to another. We need to make connection with the data source, pull the data out, work on the data like cleaning or validating it and then push into data destination. Here comes the need for Data Sources, Data Transformations and Data Destinations.

The Data Sources such as ADO NET, OLE DB, Flat File, Raw File, Excel Source, etc. help us to connect to the data source, from where we are going to pull the data out. These Data Sources use connection managers internally to define the type of data provider being used, server name, database name and login credentials, etc. in order to connect to a data source.

Read more…

Different Tabs Inside Package Designer in SSIS

When we create a new package in SSIS 2012 project, we get a package file showing following tabs in the designer area: Controls Flow, Data Flow, Parameters, Event Handlers and Package Explorer. Lets discuss about these tabs a little bit.

Control Flow:
This is the first tab in the designer. We can drag and drop different types of Tasks from SSIS Toolbox. Here we can group the tasks using different types of containers like Sequence, For Loop or Foreach Loop. We can define precedence constraints over here to give desired flow to the tasks.

Blog_1

Read more…