Hi All, i have a simple table in excel with records as $0.00, $3.50, $15.0 and i want them in sql table exactly as they appear in excel. now, i tried using data conversion task and data type as currency[DT_CY] but it gives an error. and if use data type String then it moves all records but like 0, 3.5,15. how do i do this? Thanks Nick

What do the cells actually contain in Excel? Is there a $ in the cell, or is it just formatted that way? If it's just formatted that way, there is nothing you can do about it because SSIS does not read cell formatting. However, if the cells actually contain the $ character, then you need to treat the column as DT_WSTR(255) in SSIS all the way through. Sorry, but I don't understand the 'exception' of "records like 0, 3.5, 15." ?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Todd, thanks for your reply. i dont know if its just formatted that way. cells have records with $. when i first got this requirement they told me to write Insert scripts for all tables and that was too much of work. but i dont know whats wrong with this table. i developed small package and it works for rest of the tables. e.g the table is having the following cell and when i move in sql then, Copay(in excel) Copay(in sql) $0.00 0 $3.50 3.5 $6.00 6 i wanna move it as it appears in excel otherwise i would have to manually insert each record. i tried using DT_WSTR(255) but it gives following error. columns can not covert between unicode and non unicode.

columns can not covert between unicode and non unicode. That is because Excel probably treats this column as NVARCHAR(255) and in SSIS that equates to DT_WSTR(255). I bet your SQL Column is VARCHAR (SSIS Data Type would be DT_STR) This is what I suggest: Put in a Data Conversion task and convert the Copay column to Currency, and call the new column "curCopay". In the error configuration for that task, set it to Re-direct rows. Then add a Derived Column transform and connect the red arrow from the bottom of the Data Conversion to it. In the Derived Column, add a column called "curCopay" and set the expression to "(DT_CY) 0" . Now put in a Union All that joins the output of the Derived Column with the regulare (green) output of the Data Conversion. Make sure all fields are mapped. Now when you run, you will convert the Copay to currency and if you can't (like if someone enters "n/a" for it, it will get set to 0. And you will need to change the SQL Column to Money data type.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Yes because data in excel might stored as currency and in table it might be varchar. That'z why it is storing as 0, 3.5, 6 .................