mapping the data to the access destination

• emmett-281018

  Valued Member

  Points: 50

  More actions

  January 3, 2006 at 3:55 am

  #170218

  Hello - I've just started using SSIS and need some clarification if you can spare the time that is I need to export Sql data to Access but am having problems mapping the data to the access destination (I created a table using ADOX). Any ideas?

  Cheers,

• emmett-281018

  Valued Member

  Points: 50

  More actions

  January 4, 2006 at 5:58 am

  #612568

  I've created an access db using ADOX and a table. I've attempted to connect to this newly created database using instance (of CManagedComponentWrapper) AcquireConnections method but it fails. Anyone know how to bind to a newly created access database and map source columns to the columns of the newly created table???

  private static void CreateDatabase(string currentDirectory)

  {

  // 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.ParentCatalog = cat;

  tbl.Name =

  "Currency";

  tbl.Columns.Append(

  "CurrencyCode", DataTypeEnum.adVarChar, 3);

  tbl.Columns.Append(

  "Name", DataTypeEnum.adVarWChar, 16);

  tbl.Columns.Append(

  "ModifiedDate", DataTypeEnum.adDate, 0);

  }

  #region

  Add Connection Managers

  /// <summary>

  /// Adds the OLEDB Source and Destination connection managers to the package

  /// </summary>

  /// <param name="currentDirectory"></param>

  private void AddConnectionManagers(string currentDirectory)

  {

  // Add the OLE DB Source connection manager

  ConnectionManager IRInsight = package.Connections.Add("OLEDB");

  // Set the stock properties

  IRInsight.Name =

  "OLEDBSourceConnection";

  IRInsight.ConnectionString =

  @"Provider=SQLNCLI;Integrated Security=SSPI;Initial Catalog=AdventureWorks; Data Source=DATA_SOURCE;";

  // Add the destination connection manager

  ConnectionManager accessDatabase = package.Connections.Add("OLEDB");

  // Create a destination database to connect to

  CreateDatabase(currentDirectory);

  // Connect to the newly created database

  // Set the stock properties

  accessDatabase.Name =

  "OLEDBDestinationConnection";

  accessDatabase.ConnectionString =

  @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + currentDirectory + DESTINATIONNAME;

  }

  private

  void AddOLEDBDestination()

  {

  oledbDestination = dataflow.ComponentMetaDataCollection.New();

  // set stock properties

  oledbDestination.ComponentClassID =

  "DTSAdapter.OLEDBDestination";

  oledbDestination.Name =

  "OLEDBDestination";

  oledbDestination.Description =

  "dataflow sink.";

  // associate the runtime connection manager

  // the connection manager association will fail if called before ProvideComponentProperties

  oledbDestination = dataflow.ComponentMetaDataCollection[1];

  if (oledbDestination.RuntimeConnectionCollection.Count > 0)

  {

  oledbDestination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[

  "OLEDBDestinationConnection"].ID;

  oledbDestination.RuntimeConnectionCollection[0].ConnectionManager =

  DtsConvert.ToConnectionManager90(package.Connections["OLEDBDestinationConnection"]);

  }

  CManagedComponentWrapper inst = oledbDestination.Instantiate();

  inst.ProvideComponentProperties();

  //inst.SetComponentProperty("OpenRowSet", "OLEDBDestination");

  //inst.SetComponentProperty("AccessMode", "SQL Command"); // "OpenRowset Using FastLoad"

  // map a path between the source and destination components

  dataflow.PathCollection.New().AttachPathAndPropagateNotifications(oledbSource.OutputCollection[0], oledbDestination.InputCollection[0]);

  // Acquire the connections and reinitialise the component

  inst.AcquireConnections(

  null);

  // A destination table name has not been provided.

  inst.ReinitializeMetaData();

  MapDestinationColumns();

  inst.ReleaseConnections();

  }

  #region

  Map columns from source to sink

  /// <summary>

  /// Map data columns from data source to destination table columns

  /// </summary>

  private void MapDestinationColumns()

  {

  IDTSInput90 input = oledbDestination.InputCollection[0];

  IDTSVirtualInput90 vInput = input.GetVirtualInput();

  foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

  {

  IDTSInputColumn90 vCol = instance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

  instance.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

  }

  }

  #endregion