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

Posted on 2006-04-23
Last Modified: 2013-11-30
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!

Question by:studious01
    LVL 44

    Accepted 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

    Author Comment

    bruintje,  I decided to follow through with your suggestion and it worked fine.

    Thanks again!

    - Studious01
    LVL 44

    Expert Comment

    glad it worked and thanks for the grade :)

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now