SSIS – Flat File Solution which have Blank Records – II

Introduction

In our previous version of article, we are showing that, how a SSIS Flat file source handles the blank records when retrieving the data form Flat File.

You can find the Article from:

SSIS – Flat File Solution which have Blank records

http://sqlknowledgebank.blogspot.in/2017/04/ssis-flat-file-solution-which-have.html

Before going to this article, we recommended you to go to our previous article to understand the purpose of the article. Don’t forgot to see the comments.

The previous solution is exclusively for SQL Server 2012 and onwardsas the property of Flat File Connection Manager named AlwaysCheckRowDelimiters is not present in the lower version like SQL Server 2008 R2.

One of my friend ask me a question.

“What we do in this situation for SQL Server 2008 R2/ SQL Server 2005”.

Believe me the solution is so simple with SQL Server 2008 R2 version also.

We don’t have SQL 2008 R2 and BIDS. We have SQL Server 2012 with Data tools. 

So we are going to provide the solution in SQL Server 2012.

The differences is in SQL 2012, it takes one extra Transform for filtration. But in case of SQL Server 2008 no need of that extra transform. We will identify it when we provide the solution.

The Source

We are using the same source as we used in our previous article.
The flat file source is showing bellow.

You can find the blank rows within it.

The Solution

Now it’s time to show you the simple method of solution

Step – 1 [ Data Flow Source – Flat File Source ]

Open the Flat File source editor and select the Error Output tab. Here just select Ignore Failure for Error and Truncation for all the columns. That’s the only task that you need to perform.

Step – 2 [ Use Conditional Split – Used only for SQL Server 2012 ]

This steps is especially for SQL Server 2012. We don’t need to use it if we are using SQL Server 2008 R2 version.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Flat File Solution which have Blank records

Introduction

As we have an urgent requirement of a specified SSIS solution. The common thing that a SSIS developer always faces that the data source is not perfect.  

We have a flat file and we need to retrieve records from it. 

Looks simple but have little twist in it. The flat file has some bank records and we need to retrieve records from flat file after removing the blank records.

Case Scenario

We have a flat file named TestFile contains some blank records.

It is not only contains blank file but contains space.

Trying to retrieve records

As we understand the Case scenario, we are trying retrieve the flat file by using Redirect Row at Error output of Flat File Source Editor

When we are trying to run the Solution we got the error.

If we look at the Process Tab we find the following error generated

[Flat File Source [2]] Error: Failed to get data for the error output buffer.
[Flat File Source [2]] Error: An error occurred while processing file "E:\SSiS Pracice\Files\TestFile.txt" on data row 2.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Flat File Source returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

So What the Solution is

As we find that, Redirection Rows is unable to provide solution in this case scenario.

The solution is quite simple and no need to Redirect Row for that.

We can get the solution by changing a property value of Flat File Connection.

In Connection manager

Flat File Connection à Properties

AlwayCheckForRowDelimiters = False (Default is True)

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Problem with Buffer and BLOB data type when in Migration

Introduction

In this article we are trying to discuss a about a common memory related problem at least all SSIS developer faced. This type of problem makes developer life little disturbed …just joking.

What is the Warning Message that we faced

We must remember that warning sometimes convert to Error. That’s the reason, I am writing this warning message in red color. I personally treat warning as an error as I try to remove warning from SSIS package.

[DTS.Pipline] Information: The buffer manager detected that the system was low virtual memory, but was unable to swap out any buffer. 4 buffer was considered and 4 was locked………

Danger … Danger … Danger …The SSIS package can stop within any moment of time.

Lot of the developer has the same experience when they are working with BLOB data type in SSIS package.

How we solved it

There are lot of process to solve this issue. But the thing is that which approach is best suitable for your SSIS package that you have to decide it.

Anyway, here I am providing three approach to solve it.

Approach - 1

The approach is increasing physical memory or virtual memory - can solve the problem. But it is not possible every time due to tight schedule, budget etc.

So, as a developer we have to provide solution within all this constraint and hence my next two approach is come into the picture.

Approach - 2

The BESTapproach that I recommended is load the data step by step. Means not loading the entire data at a time but go to the incremental load approach.

We must retrieve a limited amount of data at apoint of time from source not retrieve entire source data. It depends on our logic, how you retrieve data from source step by step way.

If the no of record count is limited this type of error generally not occurs. But it also depends on data type and size.

If anyone ask me for how many number of records can be retrieved at a time then my answer to him is I have no idea. Check your source table, data type …data volume, physical REM, virtual memory.

Approach - 3

Now SSIS is also provide us something to solve this problem.

SSIS provide two properties that can solve problem related to Virtual memory buffering problem.

The data flow task have this property named BufferTempStorageParh and BLOBTempStoragePath.

[Right Click the Data Flow task and choose Property… In the property window… in the MISC section you can find those two property named BufferTempStoragePath and BLOBTempStoragePath.]

By default the value of this property is BLANK.

Working with BufferTempStoragePath:

The property named BufferTempStoragePath, works when the problem of virtual memory comes.

I mean to say that in the condition of Low Virtual Memory we have to set the BufferTempStoragePath property values.

Here in the BufferTempStoragePath, we have to specify a path where the memory is dumped when low virtual memory problem arise. For Example E:\MyMemeoryDump.

Working with BLOBTempStoragePath

There is another property is equivalent to important when we are migrating data from one Server DB to another server DB with Binary Large Object (BLOB) exists in the source DB.

To working with BLOB data type data we must understand how SSIS works with it.

It just four steps activity.

Step-1: Read the BLOB contents from Source.

Step-2: Write the BLOB contents to the temporary file.

Step-3: Read the BLOB from temporary file.

Step-4: Write the BLOB contents to the destination.

So by default SSIS used C:\Windows\temp location for this activity.

Recommendation

Drive C: is active for Operating system, so it is not a good idea to put the path for property named BufferTempStorageParhandBLOBTempStoragePath in drive C:

So I recommended that don’t use the drive C and the drive that contains the Database. Use any locale good size drive for that purpose.

Hope you like it.

Posed by: MR. JOYDEEP DAS

SSIS Execute Process Task – TimeOut Property

Introduction

One of my previous article is showing, how to use the Execute Process task intelligently to solve big problem.

Here in this article I am trying to demonstrate the small but common error that people face when using Execute Process Task. It is called Timeout Error.

How Timeout Error Generate

Suppose we are using a batch file to extract file from a source location, but when we are going to extract it, the file itself is not loaded properly in source location, causing the problem of timeout, if TimeOut property is mentioned in Execute Process Task.

How It Can be Solved

The Execute Process task zero (0) as default value of the TimeOut property. That means the there is no time out for the process.

The process either completed or returns an Error to complete the Execute Process Task.

But if we define a specified the TimeOut property that means the Execute Process task must be completed within the specified time, others Timeout Error occurs.

Please remember that the TimeOut property is taken a value as SECOND.

There is another property named TerminateProcessAfterTimeOut. It takes the value TRUE or FALSE. It means if the TimeOut value Expire, is it Terminate the Process or NOT.

The TerminateProcessAfterTimeOut Property is DISABLED if we set the TimeOut value to ZERO (0).

Hope you understand it.

Posted by: MR. JOYDEEP DAS

Data Flow tasks of SSIS

Introduction

In my previous article, I am with Control Flow Tasks and Containers. In this article I am trying to discuss abut one of the control flows tasks named data flow.

If we think about a package of SSIS, it must contains one Control Flow tasks and can have zero, one or more data flow tasks.

How work with Data Flow Tasks

It is simple to work with data flow tasks. From Control Flow Items tools bar just drag the Data Flow tasks to the work space of Control Flow tabs and then double click the Data Flow Tasks.

Data Flow tasks Tool Box Objects

Data flow tasks has 3 types of Objects in tools box mentioned bellow.

1.    Data Flow Source Adapters

2.    Data Flow Transformations

3.    Data Flow Destination Adapter

Data Flow Source Adapter

It uses the package connections which points a server instance or file locations. The Source Adapter extracts data from source and moves it to direct Destination Adaptors or to Data Flow transformations and then from Data flow Transformation to Destination adapter.

Here there is one exceptions, the Raw File Adapters doesn't use any package connections. 

There are some common data flow sources adaptors are mentioned bellow.

1.    Data Reader: Make connection to a table or Query through a .NET providers

2.    Excel: Extracts data from Excel work sheet in the Excel file.

3.    Flat File: Make connection with delimited or fixed-width file.

4.    Raw File: Connect to binary type file.

5.    OLEDB: Connects to Installed OLEDB provider, such as SQL Server, Access, ORACLE etc.

6.    XML: Extracts data from XML file.

Data Flow Transformations

Transformations are used to modify or manipulate data in the data flow. It performs operations at one row at time or several rows of data at once.

There are wide verity of transformation tasks are present in the tools bar, what you choose is depends on what type of data processing is needed to complete your functionality. Depending of different nature of transformation, we can divide the transformation into 4 categories.

Category-1 [ Logical row-level transformations ]    

It's works on each and every rows of data flow one by one. `Some common used of there transformations are data type conversion, replacing NULL values, case conversion(lower case to upper case etc), row counts etc.

Category-2 [ Multi-Input and Multi-Output transformations ]    

It's works with more than one data input or can generate more than one output. It's have the ability to combine or branch data from one or more sources to one or more destination. Common examples are Union All, Merge-Join, Multicast etc.

Category-3 [ Multi-Row transformations ]    

It's performs works based on criteria from multiple inputs rows or generate multiple output rows from a single input rows. This type of transforms has more memory overhead. But from business prospective it is very important. Some common examples are Aggregate, Pivot, Sort, Row Sampling etc.

Category-4 [ Advanced Data-preparation transformations ]    

It's works on rows in the data flow pipeline. Some common examples are OLE DB Command, Fuzzy Grouping, and Scripts Components etc.

Data Flow Destination

It is the end point of the package. It is just like the Data flow source to use the package connections. It defines the destination where the processed or unprocessed data are pushed. If we take an example, suppose we want to store the final output of the package in a excel file than we must select the Excel Destination adapter.

To understand it properly

Sometimes real life examples help us to understand properly. Here I am taking a real life example to understand it properly. Think about postal services.

Step-1 [ Data Flow Source Adapters ]

Postman collects postage from different sources like post box, registered postage, and parcel from customer home.

Step-2 [ Data Flow Transformations ]

Then in the post office, they check the details of each and every postal baggage like the stamps are corrects or not, delivery address etc.. ect.. and process them.

Step-3 [ Data  Flow Destination Adapter ]

Postman goes to the delivery address and delivers the postage.   

Hope you like it.

Posted by: MR. JOYDEEP DAS

Important Facts SSIS

Introduction

Before going to deep with SSIS, we have to understand some important facts related to SSIS. I forgot it to mention in my previous articles related to SSIS. Here in this article I am trying to illustrate it. Here I am using Microsoft SQL server 2008 or 2008R2 version for SSIS solutions.

Relation with MS Visual Studio 2010

Microsoft Visual Studio 2010 does not support Business Intelligence Development Studio Integration Services, Report Services and Analysis Services projects for SQL Server 2008 or 2008R2 version. To work around this issue, you can install Visual Studio 2008 alongside Visual Studio 2010 on the same machine and then open the Business Intelligence Development Studio projects in Visual Studio 2008.

Creating Solutions In BIDS

When we are going to create a new solution, Business Intelligence Development Studio adds a Solution folder to Solution Explorer, and creates files that have the extensions are mentioned bellow.

File Extension	Description
.sln	The *.sln file contains information about the solution configuration and lists the projects in the solution.
.sou	The *.suo file contains information about your preferences for working with the solution.

Files in Integration Services Projects

When we add a new or an existing Integration Services project to a solution, Business Intelligence Development Studio (BIDS) creates project files that have the extensions are mentioned bellow

• The *.dtproj file contains information about project configurations and items such as data sources and packages.

• The *.dtproj.user file contains information about your preferences for working with the project.

• The *.database file contains information that Business Intelligence Development Studio requires to open the Integration Services project.

Related Tropics

Understand the BIDS

 http://www.sqlknowledgebank.blogspot.in/2013/01/understanding-bids.html

 SSIS Connection Manager
 http://www.sqlknowledgebank.blogspot.in/2013/02/ssis-connection-manager.html

 Introduction to Control Flow and Data Flow
 http://www.sqlknowledgebank.blogspot.in/2013/02/introduction-to-control-flow-and-data.html

SSSIS Control Flow Containers

http://www.sqlknowledgebank.blogspot.in/2013/02/ssis-control-flow-containers.html

References

http://msdn.microsoft.com/en-us/library/ms138028(v=sql.100).aspx

Hope you like it

Posted by: MR. JOYDEEP DAS

SSIS Control Flow Containers

Introduction

To continuing my journey on SSIS, this article contains related to Control Flow Containers. It is important to understand the containers of control flow. As per me very SSIS developer must have a solid concept related to the Containers of Control flow.

Type of Control Flow

There are three primary types of control flow objects.

1.    Control flow Tasks: Workflow objects that perform operational-level jobs.

2.    Control flow Containers: It’s grouping mechanisms for tasks and other containers.

3.    Constraints: Allow to connects tasks and define execution ordering and precedence.       

Control flow containers

When we open the SSIS BIDS for control flow tables and the Control Flow Items in the tools box we can see the three containers placed at the top of the others.

Microsoft places it as the top positions they are frequently used by the SSIS Developers.

Why it’s needed

A SSIS package must contains a single control flow tasks. However most of the time a package contains several control flow tasks and they coordinate each other. So in this condition we must organize the tasks and for that we use the control flow containers. So a control flow contains used to group the tasks together. The control flow containers group the tasks together for parallelization, ordering, logging and transactions.

We can think the control flow containers as a folder and contains the other sub folder and file. Here the control flow containers contain different tasks like file in the folder and others containers like subfolder within folder.

How to add containers

To add a control flow containers just drag it from control flow item tool bars to control flow work space of BIDS.

Type of Control Flow Containers

There are three primary types of control flow containers

1    .    Sequence Containers

2    .    For Loop Containers

3    .    ForEach Loop Containers

      Sequence Containers

      Most commonly used containers by SSIS developer. It’s used to organize subordinate tasks by grouping them together and let us applies transactions or assigns logging to the containers.

       For Loop Container

     Provide the same functionality as sequence container except that they can let us run the tasks within them multiple times, based on any condition. Think it as a loop. For example for loop that runs from 1 to 100 times.

      ForEach Loop Conatiners

      Allow us looping but instead of providing a condition expression we loop over a set of objects such as files in a folder.

     If we takes a simple example think we have 10 clients and they upload there data to an FTP server by 10 different txt file. The SSIS program must check the format of each txt file before read and store the data in Table objects of MS SQL Server.

Common Properties of Containers

There are some common properties of all the containers are mentioned bellow. We can find the details in MSDN http://msdn.microsoft.com/en-us/library/ms137728(v=sql.100).aspx

Let’s take a simple example to understand the Control Flow containers.

Example of Sequence Containers

1    .    Drag the Sequence Containers from Control Flow Items tools bar to Control Flow work 

         space. Drag 3 Execute SQL Task to the Sequence Containers.

2    .    Edit the Execute SQL Task by right click each tasks and select Edit from popup menu.

3    .    In SQL StatementàConnection Select the OLEDB Connection and in SQL Statement  

         property just Write SELCT 1.

4   .    In the 2^nd Execute SQL Tasks for the property SQL statement write something which in not a 

        sql statement to create Error.

5   .    Now go to the property of the Sequence containers and set the transaction option property to 

        Required.

6   .    Now run the package.

Related Tropics

1    .    Understand the BIDS

         http://www.sqlknowledgebank.blogspot.in/2013/01/understanding-bids.html

2   .    SSIS Connection Manager
        http://www.sqlknowledgebank.blogspot.in/2013/02/ssis-connection-manager.html

3   .    Introduction to Control Flow and Data Flow
        http://www.sqlknowledgebank.blogspot.in/2013/02/introduction-to-control-flow-and-data.html

References
http://msdn.microsoft.com/en-us/library/ms137728(v=sql.100).aspx

Hope you like it.

Posted by: MR. JOYDEEP DAS