The use of flat file as a source or destination is very common in SSIS. MSBI has provided Flat File Source and Flat File destination components to deal with Flat files. In case you are dealing with a fixed length flat file and want to ensure you deal with the data that has specific length of character in every row you can apply different solutions into SSIS. This article will focus on a simple way to check the flat file has valid length of character in every row.
For demonstration purpose let us assume we have following flat file. Each of the rows has a fixed length that is 30 character lengths. We want to check all rows have similar character of length i.e. 30. If there are rows where character length is not 30, they are invalid rows. We want to separate the valid rows and invalid rows.
We created a Flat File connection manager. The Format we selected is Ragged Right. We want to read out the length of character in each row in the flat file.
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
In SSIS Designer we create following SSIS package. We have a flat file source to read the data from. Next we have a Derived column component where we are adding one column that will tell the length of each row. Next we are using Conditional Spilt to separate valid and invalid rows. We have two Row Sampling components to move valid and invalid rows. We have added data viewers to see what rows have moved in each row sampling component.
In Derived column we have added one column Row_Length and applied LEN function.
In the conditional split we have added two outputs with two separate conditions on Row_length column. So all the rows where length is 30 will move to Output 1 (valid) and all rows where row length is not 30 will move to Output 2 (invalid).
We executed the SSIS package and since all rows had similar length all the rows were move to valid output.
Next we change the flat file data and tempered two rows. In one row we have more than 30 character of length and in one row we have less than 30 character of length.
We executed the SSIS package again and this time we have 12 rows moved into valid output and 2 rows moved into invalid output.
So our purpose to validate the flat file length is achieved and solution work as per design.
There could be a different solution like Script component that we can use to solve this but nevertheless the point I wanted to show is to how we can achieve this using Conditional Split and without writing any code.
Thanks for reading this post.
