How to Avoid Package Design Flaws When Sourcing Data From Flat Files

As developers of SQL Server Integrations Services (SSIS) solutions, we have more than likely configured Flat File Connections as sources in our Data Flow Tasks. Whilst any unforeseen changes to the structure and formatting of flat files will invariably cause SSIS packages to fail, as developers we can still do a lot in reducing unnecessary SSIS package failures relating to data coming out of flat files. In this article, we offer recommended development practices to some flat file source errors that occur as a result of poor SSIS development practices.

Flat File Source Error #1: The system cannot find the file specified

This typically happens when a hardcoded flat file connection specified at design time is not found by SSIS during package execution. For instance, in the package shown in Figure 1, I have configured my Data Flow Task to extract a list of nominated players for the 2016 FIFA Ballon D’Or.

Figure 1

Prior to executing the package shown in Figure 1, I momentarily relocated the flat file specified in the FIFABallonDOr connection to another location. This causeed the package to fail when I later execute it as shown in Figure 2.

Figure 2

A summary of the error messages returned in Figure 2 is shown in Figure 3.

Figure 3

Recommended Practice #1: Iterate Flat Files Using ForEach Loop Container

Although for the purposes of simulating this type of an error, I deliberately moved the flat file into another location, the fact is there are plenty of reasons for a flat file not to be found in a specified path (i.e. specified drive becomes corrupt, account access to flat file is revoked etc.). Thus, as developers we must prepare for such errors and one best way to avoid such an error is to use a ForEach Loop Container. The ForEach Loop Container can iterate through flat files and execute the Data Flow Task only when files are found. Figure 4 shows our updated test package using the ForEach Loop Container – as it can be seen, the Data Flow Task is not executed as the flat file was not found.

Figure 4a

Flat File Source Error #2: Data conversion failed

There are two areas of the SSIS package that can be affected by the Data conversion failed error. The first part is between the actual raw text file and the flat file connection manager. During the setup of my sample flat file connection manager, the two fields coming from the FIFABallonDOr file were assigned a default length of 50, as shown in Figure 5.

Figure 4b

However, having later edited the FIFABallonDOr file shown in Figure 5 to include another fictitious nominee in line 5, the package execution fails at Flat File Source component shown in Figure 6.

Figure 5

Figure 6

The reason for this failure is due to possible data truncation errors within the Nominee column as shown in Figure 7.

Figure 7

The second cause of the Data conversion failed error is that even though you may have adjusted the length of the Nominee column to accommodate my fictitious entry, if you don’t refresh the metadata of the Data Flow Task’s Flat File Source component the package will fail because of the synchronisation warning (outdated column metadata) shown in Figure 8.

Figure 8

Recommended Practice #2: Redirect Truncation Rows

There is no worst or best practice when dealing with the warning shown in Figure 8, you simply have to refresh the metadata of the Flat File Source component and when that happens, you will receive a popup dialog to confirm the metadata refresh as shown in Figure 9.

Figure 9

Furthermore, there is no perfect solution to handling the error shown in Figure 7 – one possible solution is to configure an error output by adding another Destination component that will store redirected error rows from source as shown in Figure 10.

Figure 10

You would then have to configure the Error Output setting in Flat File Source Editor to Redirect row as shown in Figure 11.

Figure 11

After executing the package again, the fictitious row data should be redirected to the error table as shown in Figure 12.

Figure 12

You can double-check this by querying the error table which should return the offending record as shown in Figure 13.

Figure 13

The only problem with this workaround is that there could still be a possibility that the offending record is also longer than the length of the corresponding field in the error table, in which case the redirecting of error rows would still fail due to Data conversion failed error.

Flat File Source Error #3: The process cannot access the file because it is being used by another process

Another common error relating to working with flat files in SSIS is that you could be processing a file that is still being used by another process. This typically happens when you attempt to process a flat file that is either open or currently being written into by another process. When such a scenario occurs, your SSIS package will throw an exception similar to the message shown in Figure 14.

Figure 14

Recommended Practice #3: Use Script Task to Check for Flat File Lock

When data is being read out of a flat file, all that SSIS is doing is merely calling the StreamReader Class from System.IO namespace. Thus, we need to find a mechanism in SSIS Package that will make use of the StreamReader Class to ensure that you are not processing files that are at the time locked by another process. One such mechanism is the Script Task wherein you can set package variables based on the output of the StreamReader Class. To demonstrate such a mechanism, I have updated our test package to include a Script Task step shown in Figure 15.

Figure 15

The definition of the Script Task’s Main() method is shown Script 1. The script basically set package variable islocked to either true (indicating that flat file is available for use) or false.

public void Main() {

try {

string line;

System.IO.StreamReader file = new System.IO.StreamReader(@ "C:\temp\New folder\FIFABallonDOr.txt");

while ((line = file.ReadLine()) != null) {

Dts.Variables["User::islocked"].Value = true;

break;

}

file.Close();

} catch (Exception ex) {

if (ex.InnerException.Message.Contains("because it is being used by another process")) {

Dts.Variables["User::islocked"].Value = false;

}

Dts.TaskResult = (int) ScriptResults.Success;

}

Script 1

Package variable islocked is then used to evaluate the Expression of the Precedence Constraint that joins the Script Task and the Data Flow Task as shown in Figure 16.

Figure 16

Conclusion

The flat file is one of the commonly configured sources of data. However, you may find yourself frequently having to troubleshoot a failing package as a result of poorly designed SSIS package. Fortunately, this article has provided solutions that could be used eliminate some of the errors relating to flat files.

Downloads

Sifiso Ndlovu

Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

Sifiso's LinkedIn profile

View all posts by Sifiso W. Ndlovu