Has anyone found problems with transforming data from a SQL (Server) source to flat files, only starting with SP3?
We've got a problem which we thought was due to editing SP1 created packages with SP3 applied to the client, but we've narrowed it down to it being SP3 specific.
Basically we have done the following:
1. Create a new DTS package on a local server (Enterprise Manager - Data Transformation Services, Local Packages, right click, New Package)
2. Create a source connection, setting the Data Source to MS OLEDB Provider for SQL Server. Leave the server as local, and select a database. NT Authentication was used for the sample. Set it to a small table on the same SQL Server box as the DTS package is being created on. Use the whole table as the source, don't specify a query.
3. Create a destination file on the local box, and set its location to c:\testDTS.txt. Leave the file format settings at their default.
4. Click on Task menu... Transform Data Task... and select Source Connection and Destination Connection.
5. Double click on the Data Transformation task to set the column mappings.
6. Leave the source settings at their default (select a different table if the first one is too big to export)
7. Click on Destination tab and Click on the Populate from Source button, followed by the Execute button.
8. Click on the Define Columns button
9. After a few seconds, an MMC error is displayed, which is repeatable every time this procedure is followed.
Further investigation has shown that SP1 packages do not seem to exhibit this behaviour, unless they are being edited by a client that has SP3 applied.
The database software is SQL Server 2000 Developer Edition with SP1 applied.
The OS is Windows XP Professional with SP1.
Any feedback from others with this fault would be much appreciated, but I can't believe given the simplicity of the test that someone has not already encountered this since the release of SP3. I've built literally hundreds of DTS packages, but have only encountered this problem with SP3.