While building an ETL package, it’s very important to control the flow of work as per the requirement. And this can be done by using a SSIS component called Sequence container. The sequence container is a part of Control Flow and a sub part of the global container i.e. Control Flow container. Here we can set the flow of our package by precedence constraints.
The Sequence Container is basically used for grouping same type of tasks and to control them under a separate group. It can group the whole contents of the package into multiple separate control flows. The sequence container can be nested to any level to give more clarity to the process flow.
Here in this article, we will see the steps to control the tasks using Sequence container as well as how to do the same by programming Integration Services.
Read more…
SQL Server provides us with a powerful Data Integration service called SSIS. But to make it more robust and effective, we need to monitor some of the vital measures so as to increase its performance, troubleshooting errors and to keep track of the data flow. The best way provided by SSIS to accomplish this is SSIS Logging. SSIS will log entries in the log file or table on the basis of the events that occur during the execution of the package.
Here in this section, we will tell you about different features of SSIS logging and the implementation of the same by programming Integration Services.
Read more…
Lookup Transformation is used to lookup data by joining data in input columns with columns of a reference table. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL statement. The lookup is used to access additional information in a related table that is based on values in common columns.
For Example:
Let us have one employee table and one transaction table. The employee table contains information about all the employees. And the transaction table keeps record of the type of transaction done by any employee.
Read more…
There may be some circumstances in a sanctuary where it becomes necessary to keep a leash of foxes and a pack of wolves separate so that they must not fight with each other and make things a mess. Similarly, in the database jungle we do the same things by using some conditional statements in order to keep things distinct. SSIS provides a nice transformation to do the same, i.e. Conditional Split Transformation.
By using this conditional split transformation, the incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions.
Read more…
Minimized costs, increased effectiveness, higher flexibility and easy management have become keys to success in the IT industry today. This page aims at achieving the above by introducing a new approach to solve a set of problems faced in ETL process.
The IT industry is ever expanding, and elements of IT can be found in almost every sector, every company. Whether you are playing on a website or using advanced computerized utilities on a farm, knowledge of IT and programming is going to be very useful. Computer skills are necessary all over the world these days, from basic knowledge to complex procedures. Processing a set of operations with different source and target names, but same structure and same set of transformations is a common scenario in development areas. So approaching an automated methodology for the same using some programming languages like C# .Net or VB .Net with little or no modifications is better. This can reduce the total work load up to 80 – 90%.
This page deals with the approach of creating the ETL Package by using C# .Net and VB .Net language. As SQL Server 2005 is a product of Microsoft Corporation, we got the whole set of APIs in Microsoft Visual Studio. Using these APIs and some standard procedures and scripts in T-SQL, we can ease the ETL Process to increase the productivity.
Read more…
Lots of things really matter when building an efficient and robust database and its corresponding package for extracting and loading of data. And this matter more when the database size is very big (or huge).
It’s high time when we need some method to optimize the extraction and loading process in the package. This means increasing the performance of SSIS package and which in turn makes your package to be able to retrieve and update the data they want very quickly.
The key component for faster extraction is to put indexes in the target table but it will give you less performance when you load data on it. So when to put indexes and where to put indexes is very important. Insert indexes to the joining columns before starting extraction, especially to those that have data types other than integer like varchar, nvarchar, datetime, etc.
Secondly, the data type chosen for a particular column also plays a significant role in the performance of your SSIS package. Choose data type and size of the columns wisely.
Read more…
Recent Comments