DTS Packages Sometimes Make Moving Data Tricky

Microsoft SQL Server’s Data Transformation Services (DTS) packages are sometimes tricky, especially when an IDENTITY column is involved. The simple task of moving data from a flat-file source, such as a standard text file, to a SQL Server table with an IDENTITY column can be the source of much frustration if you’re unaware of the behind-the-scenes actions of DTS.

When adding rows to such a table, you have two options. You can provide a value that SQL Server will use to populate the IDENTITY column, or you can let SQL Server will provide a value for the field. Given the popularity of the latter method, you need to be aware of the common pitfalls.

The most common problem involves the DTS option labeled Enable identity insert. This option should be enabled if you intend to let SQL Server add the values to the IDENTITY column. You also need to ensure that the IDENTITY column isn’t listed in the Selected columns list on the Destination columns tab of any transformations defined within the package.

— Jason Cochran
[email protected]

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.