Stubborn column data type in SSIS flat flat file connection manager won't change. :(

Question

I have inherited an existing SSIS package that imports flat file data into my SQL Server 2005 database. I need to change the data type of the "Gross Sales" column from "numeric" to "currency". When I change the data type and export the package the data type remains numeric.

I have also tried creating a new flat file connection to use in the same package, but for some strange reason it still remains numeric instead of "cy" currency. I guess there is something "stuck" in some other area that is forcing the last column to remain numeric?

Does anyone know the trick to changing the data type for a flat file data source?

Answer 1

Thanks for all the help everyone. It looks like in my case I needed to delete and re-add the flat file source step and add a new flat file connection manager. Maybe there was a better way to do it and I was just clicking in the wrong order in the GUI maze in SSIS. :D

Answer 2

Did you try adding a Data Conversion Transformation Task in your DT?.

e.g. You can modify the Gross Sales as

Gross Sales, Gross Sales_CONV    Choose Currency [DT_CY] as your currency.

Answer 3

I've also found that sometimes changing ValidateExternalMetaData to false on the Source and Destination properties will help.

Answer 4

When some changes don't apply, even though there's no apparent reason for the data to be "stubborn" and resist change, you should try closing Visual Studio and open it back again. In my case it works for this mentioned failure, as well as when the Script Editor won't open. It looks like some information remains on cache and prevents some functionalities from working properly. In my case I had a lot of columns in my flat file connection manager, so deleting the connection and adding those columns back again one by one was the last thing I would try.