Monday, 3 April 2017

SSIS – Flat File Solution which have Blank Records – II

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

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

No comments:

Post a Comment