CODEWITHSUNDEEP
sqlsql-serverssisodbcvisual-foxpro
Banjer_HD
Banjer_HD

Reputation: 300

The column attribute "ORDINAL_POSITION" is not valid [Import and Export Wizard - Visual FoxPro ProviderDescriptors.xml SSIS]

I am trying to sync a Visual FoxPro database to a SQL Server database.
I am using the SQL Server Import and Export Wizard (from SQL Server Data Tools).

When I click "Edit Mappings..." I first had 4 errors with the same problem, then I changed a few values in ProviderDescriptors.xml and ended up with this:

<dtm:ColumnSchemaAttributes
    NameColumnName = "COLUMN_NAME"
    OrdinalPositionColumnName="ORDINAL_POSITION"
    DataTypeColumnName = "TYPE_NAME"
    MaximumLengthColumnName = "LENGTH"
    NumericPrecisionColumnName = "PRECISION"
    NumericScaleColumnName = "SCALE"
    NullableColumnName="NULLABLE"
    NumberOfColumnRestrictions="4"
/>

Now the only error is on OrdinalPositionColumnName
Error: The column attribute "ORDINAL_POSITION" is not valid

Which value to use for OrdinalPositionColumnName (when using the Visual FoxPro ODBC driver)?

Thanks for helping!

Upvotes: 0

Views: 526

Answers (2)

JLN
JLN

Reputation: 21

I know it is a bit late but I came across your question while searching for the answer myself, and I wanted to document it here in case there is anyone else in my position in the future that searches this issue and finds this page.

The answer is Radix

in context, it looks like this:

    <dtm:ColumnSchemaAttributes
        NameColumnName = "COLUMN_NAME"
        OrdinalPositionColumnName="Radix"
        DataTypeColumnName = "TYPE_NAME"
        MaximumLengthColumnName = "LENGTH"
        NumericPrecisionColumnName = "PRECISION"
        NumericScaleColumnName = "SCALE"
        NullableColumnName="NULLABLE"
        NumberOfColumnRestrictions="4"
    />

Upvotes: 2

Cetin Basoz
Cetin Basoz

Reputation: 23797

Here is a sample:

void Main()
{
    string sqlConnectionString = @"server=.\SQLExpress;Trusted_Connection=yes;Database=Test";
    // c:\MyDataFolder\myTable.dbf
    string dataPath = @"c:\MyDataFolder";
    string tableName = @"MyTable";

    Stopwatch sw = new Stopwatch(); // just for timing the sample run
    sw.Start();

    using (OleDbConnection cn = new OleDbConnection("Provider=VFPOLEDB;Data Source=" + dataPath))
    using (SqlConnection scn = new SqlConnection(sqlConnectionString))
    {
        OleDbCommand cmd = new OleDbCommand($"select * from {tableName}", cn);
        SqlBulkCopy sbc = new SqlBulkCopy(scn, SqlBulkCopyOptions.TableLock, null);

        // Needed if VFP-SQL Server tables' field count or names are different
        // ie: LName in VFP might be LastName in SQL server
        // or VFP might have 10 fields when SQL server 8
        // VFP column names on left, SQL server column names on right
        sbc.ColumnMappings.Add("Category", "[Category]");
        sbc.ColumnMappings.Add("Activity", "[Activity]");
        sbc.ColumnMappings.Add("PersonId", "[PersonId]");
        sbc.ColumnMappings.Add("FirstName", "[FirstName]");
        sbc.ColumnMappings.Add("MidName", "[MidName]");
        sbc.ColumnMappings.Add("LastName", "[LastName]");
        sbc.ColumnMappings.Add("Email", "[Email]");

        cn.Open();
        scn.Open();

        // Since this is a sample, instead of wrting to a table
        // creating and writing to a global temporary table
        // You can see this table's data, connecting to the same database (global temp)
        SqlCommand createTemp = new SqlCommand();
        createTemp.CommandText = @"create table ##PersonData 
  (
    [Id] int identity primary key,
    [Category] varchar(50), 
    [Activity] varchar(50) default 'NullOlmasin', 
    [PersonId] varchar(50), 
    [FirstName] varchar(50), 
    [MidName] varchar(50), 
    [LastName] varchar(50), 
    [Email] varchar(50)
  )";
        createTemp.Connection = scn;
        createTemp.ExecuteNonQuery();

        OleDbDataReader rdr = cmd.ExecuteReader();

        sbc.NotifyAfter = 100000;
        sbc.BatchSize = 1000;
        sbc.BulkCopyTimeout = 10000;
        sbc.DestinationTableName = "##PersonData";

        sbc.SqlRowsCopied += (sender, e) =>
          {
              Console.WriteLine("-- Copied {0} rows to {1}.[{2} milliseconds]",
          e.RowsCopied,
          ((SqlBulkCopy)sender).DestinationTableName,
          sw.ElapsedMilliseconds);
          };

        sbc.WriteToServer(rdr);

        if (!rdr.IsClosed) { rdr.Close(); }

        cn.Close();
        scn.Close();
    }
    sw.Stop();
    Console.WriteLine($"Done in {sw.ElapsedMilliseconds} milliseconds.");
}

Upvotes: 1

Related Questions