Working with Text Data Sources

SSIS won't recognize it's working with a flat file, so you'll need to manually prod it to do your bidding. Here's how.

A common source for SSIS packages is the flat file. These are just plain text files that are delimited by some character such as a comma, pipe or tab. The problem is when you connect SSIS to a flat file, it assumes a few things about the file, specifically the data types of all the columns.

Flat files do not contain metadata so SSIS has no good way to know the data type. Further, SSIS does not look at the data and attempt to make an educated guess. What you end up with is all the columns configured with a string data type of length 50. This can cause conversion issues if your data is not a sting or truncation errors if you have more than 50 characters.

Luckily, there is a solution. Once you have set up your flat file connection, select the Advanced page in the properties dialog box as shown in Fig. 1.

Advanced page in the Flat File Connection Manager Editor dialog
Figure 1. Set up properties for connecting to your flat file via the Advanced page in the Flat File Connection Manager Editor dialog. (Click image to view larger version.)

From here you can select each column and then change the data type and its associated properties, such as length, precision and scale. You can also change the column’s name which comes in handy when it is not included in the first row of your file.

Once you have made all the appropriate changes, SSIS will know what type of data you have and the names of all the columns.

It is much easier to make these changes in the connection than to try and use data conversion transformations later in your package. So do yourself a favor and spend a few minutes cleaning up your text data sources, you will be much happier and see fewer data problems later on.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Microsoft Warns SameSite Cookie Changes Could Break Some Apps

    IT pros could face Web application issues as early as next month with the implementation of a coming SameSite Web change, which will affect how cookies are used across sites.

  • Populating a SharePoint Document Library by E-Mail, Part 1

    While Microsoft doesn't allow you to build a SharePoint Online document library using e-mail, there is a roundabout way of getting the job done using the tools that are included with Office 365. Brien shows you how.

  • Microsoft Previews New App Reporting and Consent Tools in Azure AD

    Microsoft last week described a few Azure Active Directory improvements for organizations wanting to connect their applications to Microsoft's identity and access service.

  • Free Software Foundation Asks Microsoft To Release Windows 7 Code

    The Free Software Foundation this week announced that it has established a petition demanding that Microsoft release its proprietary Windows 7 code as free software.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.