SSIS - Flat File Connection Manager Behavior when all the columns in a data row don't have entries

I hope some of you might not have faced with this feature of Flat File Source Connection Manager in SSIS, where Flat File Source cannot handle the delimited data if the column values are restricted to only some of the columns in each row.

Consider you have data in the file as below:

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V1

0123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

When we create Flat File Connection Manager in SSIS, use above file data as source.

With Column Delimiter as “^ “and Row Delimiter as “{CR}{LF} “.

Once the configuration has been made and when you see Preview it shows only 3 Rows and not all the 4 Rows.

This behavior is because of the fact that SSIS Column Delimiter gets first preference and then Row Delimiter.

Consider our File Data Now : 

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

Totally there are 7 “^” s, So Connection Manager Expects 7 “^”s and the above row will be shown normally.

0123^4567^01^20171401^XXX^V1

In this row it finds only 5 “^”s, so it thinks the row is not yet over and continues till it finds two more “^” s. It will ignore the row delimiter as it is on its way to find two more “^”s.

0123^4567^01^20171401

Here it will append this row to previous row as it thinks previous row is incomplete and after two “^”s once it reaches 7^th “^”, It will append rest of the data in this row up to row delimiter to previous row.

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

For this row, everything is normal hence it will be shown correctly as one full row.

So if you do the preview with this file you will see as below now,

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

0123^4567^01^20171401^XXX^V10123^4567^01^20171401

0123^4567^01^20171401^XXX^V1^2345.89^xyzabcd

In this case, what needs to be done is :-

 SET AlwaysCheckForRowDelimiters Property of connection manager to true (If it's not set by default) 

and you will see all rows behaving normally when you do the preview.

By default, the flat file connection manager always checks for a row delimiter in unquoted data and starts a new row when a row delimiter is found. This enables connection manager to correctly parse files with rows that are missing column fields.

In some cases, disabling this feature may improve the package performance. You can disable this feature by setting the flat file connection manager property, AlwaysCheckForRowDelimiters, to False.

This is not present in documentation till SQL 2008 R2 SSIS.

NOTE : If the file data is as above and AlwaysCheckForRowDelimiters Property is set to False Then the output data you will be getting same as in preview for most of the rows and the data flown across multiple steps in SSIS flow will go wrong.

Download Files from Web using SSIS

Often we are required to load data from files (text/CSV/excel etc.). Sometimes these files are available as a download in a web server. In such cases the using a script task in SSIS comes in handy to download the files. Here is the code snippet to download the files from web in a Script Task in SSIS

Firstly, we need to create a couple of variables,

1.     Download URL path

2.     File Save Path

Once these are created, one can easily use configurations to make these variables configurable at run time. Now coming to code part where we are downloading the files,

Firstly we need to add assembly reference to System.Net namespace

using System.Net;

Write a function as below

private bool DownloadFile(string strFileName)
{

bool retVal = false;

bool fireAgain = true;

WebClient wc = new WebClient();

string strServerFilePath = "download_url from SSIS variable goes here"+strFileName;

string strLocalFilePath = "File Save Path from SSIS variable goes here"+strFileName;

wc.Credentials = new NetworkCredential("username", "password");

try

{

wc.DownloadFile(strServerFilePath, strLocalFilePath);

retVal = true;

}

catch (WebException ex)

{

fireAgain = false;

Dts.Events.FireInformation(0, "Downlad File", "Download failed: " + ex.Message, string.Empty, 0, ref fireAgain);

retVal = false;

}

return retVal;

}

Call this function from the Main function in the Script Task and verify if the function returns a true/false, and handle the load accordingly.