THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Working with Text Data Sources

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 below.

clip_image002

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.

Published Tuesday, October 05, 2010 8:00 AM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

www.amazon.com said:

Eric Johnson : Working with Text Data Sources

October 8, 2014 1:14 AM
 

batman cufflinks said:

Eric Johnson : Working with Text Data Sources

October 10, 2014 12:53 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement