2

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?

LCJ
  • 22,196
  • 67
  • 260
  • 418
programmer
  • 1,096
  • 1
  • 13
  • 31
  • What is the column type of the destination? My reason behind that question is why worry about changing the data type of source, your focus would be what data type it is when it reaches the destination. –  Aug 05 '12 at 04:32
  • The data type of the destination was decimal(9,2), but I changed it to "money" to allow for some new data that is larger (someone entered 6 billion in sales and then voided it, yay!). I made a simple little test package to reproduce the error and found that the SSIS package is aborting due to it's expectation on the flat file data. – programmer Aug 05 '12 at 04:39
  • Been a while since I worked with flat files, have you tried just doing a data flow transformation to convert it to currency. –  Aug 05 '12 at 04:58
  • 2
    You created a new flat file connection manager and switched it in the Flat File Source, yes? Right click on the line coming out of that source and select "view metadata" (I think) Does it show the type as Currency there or does it remain numeric? – billinkc Aug 06 '12 at 02:50
  • @billinkc, thanks for the tip! I didn't realize I could view the metadata like that, I just started working with SSIS. – programmer Aug 06 '12 at 16:01

4 Answers4

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

programmer
  • 1,096
  • 1
  • 13
  • 31
0

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.
WaZ
  • 1,757
  • 4
  • 19
  • 27
  • Obviously the above can only be applied to a DT. Not sure why the data type doesn't change during import. – WaZ Aug 06 '12 at 10:14
0

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

Melanie
  • 3,021
  • 6
  • 38
  • 56
0

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.

Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
Javier D.
  • 75
  • 1
  • 10