• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

MS DTS - Different data types between source data and destination table

Hello Experts,

I  am trying to use MS DTS to import data from a text file into a table residing on a database, but I've noticed that the data in the first column from the text file being imported, has a numeric data type contains numbers and letters, but the data type of the field in the table that data is being imported to, is Numeric (28,0) [Customer_ID field]. And if I attempt to Alter the table to change the Data type to Varchar, I get an error message indicating that this ALTER STATEMENT would violate the established PKY Key contstraints.  

I would greatly appreciate suggentions that would faciliate in the mapping or transferring of this column data to the appropriate column within the SQL Server 2000 Database.

Thanks Experts!

  • 2
1 Solution
Hello studious01,

changing the primary key with an alter means you really do not care about the key at this point?
then why not remove the key with all consequences like other tables relying on this key also loosing their foreign key etc....

next alter the datatype for this column and import the data, then add a primary key again and relink all other tables

but i still wonder if this is what you want, maybe it would be better to import the data into a new table then add the rows that are correctly formatted to the original table or if this is really the new format then add the rows from the original table to the new table and link all depending tables to this new table this way you always can go back to the previous setup if needed with your data and keys intact

hope this helps a bit
studious01Author Commented:
bruintje,  I decided to follow through with your suggestion and it worked fine.

Thanks again!

- Studious01
glad it worked and thanks for the grade :)

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now