Flat file data types - defined from Table?

This question is around how we can get the data types and lengths populated into the flat file source columns.

In Connection Manager, you have your flat file defined. You can choose "Suggest Types...", and the minimum lengths and correct data types will be returned from within the data in the flat file.

Is there some way to automate this data type definition, but coming from the other direction (coming from the destination table that we are loading)?

For example, you have mapped the columns that will be loaded. Can you then reverse engineer the data types and lengths for the columns in the flat file from the destination table?

TIA

No, not possible. Unless you're going to write your own package using the SSIS API. But then, if you're going to do all of that work, SSIS may not be the best fit after all.|||

Phil

Thanks much for your answer. Maybe I'm not thinking flexibly enough regarding the solution.

It seems the flat file will pretty much default to DT_STR with length of 50 for all columns. Obviously some data could be truncated.

Where I'm trying to head is:

1. Assuming your table is mapped correctly to data types and lengths, would it not be best practice to set the same data types and lengths in your flat file definition? Or...does it matter?

2. Again, back to flexible thinking about the problem and solution. Is there another path that I'm not seeing? Perhaps the definition of data types and lengths for the flat file columns is moot, b/c it is also defined (and overridden) at another point, such as during the mapping or transforms?

Thanks again

|||

Alfred Kokintz wrote:

Phil

Thanks much for your answer. Maybe I'm not thinking flexibly enough regarding the solution.

It seems the flat file will pretty much default to DT_STR with length of 50 for all columns. Obviously some data could be truncated.

Where I'm trying to head is:

1. Assuming your table is mapped correctly to data types and lengths, would it not be best practice to set the same data types and lengths in your flat file definition? Or...does it matter?

2. Again, back to flexible thinking about the problem and solution. Is there another path that I'm not seeing? Perhaps the definition of data types and lengths for the flat file columns is moot, b/c it is also defined (and overridden) at another point, such as during the mapping or transforms?

Thanks again

Generally, if a flat file column contains integers, you will want to define it as such up front in the flat file connection manager. The more work you do there, the easier the rest of the puzzle will be.