Tuesday, March 27, 2012

creating access database with tables fail in SSIS package

I'm writing a package for SSIS and need to create a destination access database with a table on the fly. I've tried the code below - whcih works to create a database - but it doesn't create a table in that database to send data to. Instead of tbl.Parentcatalog = cat I've also used cat.Tables.Append(tbl) but this fails with a type problem. What is going wrong here?

private static void CreateDatabase(string currentDirectory)

{

if (!File.Exists(currentDirectory + DESTINATIONNAME))

{

// Create Database

ADOX.Catalog cat = new ADOX.CatalogClass();

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME);

// Need to add columns using CREATE TABLE

ADOX.Table tbl = new ADOX.TableClass();

tbl.Name = "Currency";

tbl.Columns.Append("CurrencyCode", DataTypeEnum.adVarChar, 3);

tbl.Columns.Append("Name", DataTypeEnum.adVarWChar, 16);

tbl.Columns.Append("ModifiedDate", DataTypeEnum.adDate, 24);

tbl.ParentCatalog = cat;

}

}

replaced adVarWChar with DataTypeEnum.adWChar and it worked.

No comments:

Post a Comment