FIX: Visual C++ ATL Provider Fails when Used with SQL DTS

RESOLUTION

To resolve this problem, upgrade to Microsoft Visual Studio 6.0 Service Pack 3.0.

Also, to get DTS to import from the default ATL wizard-generated provider, do the following:

1. In the Select Source Tables window, the SQL Destination table name is shown as [databasename].[dbo].[*]. Change the table name "*" to another valid identifier.
2. Change the destination data-types. This can be accomplished in one of two ways:
   • Change the T-SQL Syntax
     
     
     1. Click Transform in the Select Source Tables window. The Wizard shows decimal destination type for FileAttributes, FileSizeHigh, and FileSizeLow columns in the source.
     2. Change data type of FileAttributes to Varchar(4).
     3. Change data type of FileSizeHigh and FileSizeLow to int.
   • Change the provider column map
     
     In the file that has the declaration for CFilesWindowsFile (FilesRS.h) change the property map for the rowset to more accurately specify the source datatypes.

     PROVIDER_COLUMN_ENTRY_FIXED("FileAttributes", 1, DBTYPE_I4, dwFileAttributes)
     PROVIDER_COLUMN_ENTRY_FIXED("FileSizeHigh", 2, DBTYPE_I4, nFileSizeHigh)
     PROVIDER_COLUMN_ENTRY_FIXED("FileSizeLow", 3, DBTYPE_I4, nFileSizeLow)
     PROVIDER_COLUMN_ENTRY_STR("FileName", 4, cFileName)
     PROVIDER_COLUMN_ENTRY_STR("AltFileName", 5, cAlternateFileName)
     							

3. Complete the remaining steps and the data should be copied correctly.

Another workaround is to specify SQL Server as the data source instead of ATL provider in the DTS Import Wizard. Then, select the Use a query to specify the data option and use a distributed query to retrieve data from the ATL provider. Following is an example query:

Select * from Openrowset ('MyProv.MyProv', '','c:\*.*')
				

NOTE: You must apply the fixes in Service Pack 3.0 to enable the provider to work with a SQL distributed query.