Mapping Data Type for CSV files

Question

Is it possible to save a mapping file which SSIS can use to decide the data type based on the column names rather than going and updating it in the Advanced section of the 'Flat File Connection Manager Editor'. Thank you

Answer 1

This is a common problem faced by every SSIS developer. Whenever you make changes in a flat file connection, you lose all data type mappings and you have to manually edit this by using the advanced editor.

But you can save your life using the following:

Practice #1

When you work with an existing connection, make sure you have the flat file at the reference location of the flat file connection with the same name. If you forget to save it or don't find it, try the second practice.

Practice #2

Follow the steps below before using the SSIS package:

1. Open package in XML file format.
2. Find the flat file connection.
3. Read the file name and path of flat file connection.
4. Get the output copy of the final output file (usually you can find where SSIS is exporting final output file).
5. Copy the final output file and rename with the file connection's file name and paste it to the flat file connection location.
6. Remove all the data from file except the column list (make sure you keep the file format as it is, e.g., CSV or Excel).
7. Close the XML of the SSIS package & close the package.
8. Reopen the SSIS package and you saved your life.

This trick works for me in all my cases.