Hi,

I have a field which data type is wrongly defined. I would like to change it. However, SQL Server unable to convert it and complaint unable to convert type X to type Y even though that is no data on it (but got records) via DDT.



As I know, we can use copy method as workaround - Create new table, APPEND back data to new table, drop old table and rename new table with old table name. But how to do it using DDT?



Thank you

Hi,

I would have thought the DDT would have converted the data type provided SQL permitted it. However, I wonder would this work as it would allow you to add a new field to the existing table without having to create a new table:-

1. Rename the existing field and give it a temporary name.

2. Build a new package and update your database.

3. Delete the rename history in the DDT.

4. Add a new field to the existing table with the correct data type and the original name.

5. Build a new package and update your database - this will give you a new field with the correct name and datatype and the old field with the wrong data type and a different name.

6. Copy the data from the temporary field into the new field.

7. Delete the temporary field in the DDT.

8. Build a new package and update your database - this will remove the field with the wrong data type.

9. Delete the delete field history in the DDT.

This would mean you don't need a new table.

Aaron

Hi,

Thank you reply.

The steps you shared seem required manual work, such as copy the data from old field to new field. Am I right?

Anyway I can "automate" it?



Thank you

Well, there are certain data types that cannot be converted without manual intervention.  Others can be converted.  For example, converting a VarChar into an Integer is difficult as the data within the VarChar is not going to reliably convert.  In fact, if you do this within SSMS it will not always convert the data over as well.  However, SSMS will do this for you in more scenarios than the DDT or SMO.  So if this is an isolated issue, you may consider using SSMS to modify the table to "fix" the improper definition to retain the data then the DDT will work from that point forward.

The other option is to create a pre deployment script to fix this particular table prior the structures being deployed.