Post reply

Can I modify a DTS connection dynamically from MS Access

  • August 29, 2006 at 7:15 am

    #68434

    Hi

    I have a small Access Project that executes a DTS package to import data from an Excel spreadsheet to a SQL server table.  The routine works fine but always imports from the same Excel spreadsheet specified in the DTS connection.  I would like to dynamically modify the connection so that the user can input a filename at run time but I can't see a way of doing this from within Access.  Can anyone help?

    Thanks

    David

  • August 29, 2006 at 1:59 pm

    #657382

    There is something called "Dynamic Properties Task" in DTS that could be used to dynamically change the connection property. It can use an ini fil or run a query..

    Second option in access to to get the excel spread sheet name(and Location) and copy it to a different location and use DTS to import the data from the "new location". Every time DTS will import from the "new location" but access as part of the prompt will copy the file to the "new location".

    Thanks

    Sreejith

  • August 31, 2006 at 2:48 am

    #657747

    Yes, you can change the connection dynamically. I have given a c# code for the same. Conversion from c# to access is very simple

     

    DTS.Package2Class package =

    new DTS.Package2Class();

    string filename = System.Windows.Forms.Application.StartupPath + @"\DTS\" + strfilename +".dts";

    string password = null;

    string packageID = null;

    string versionID = null;

    string name = strfilename;

    object pVarPersistStfOfHost = null;

    package.LoadFromStorageFile(

    filename,

    password,

    packageID,

    versionID,

    name,

    ref

    pVarPersistStfOfHost);

    package.FailOnError =

    true;

    #region

    connection

    for (int k = 1; k<= package.Connections.Count;k++)

    {

    if (package.Connections.Item(k).Name == "DBNme")

    {

    package.Connections.Item(k).DataSource = strDTSServerName;

    package.Connections.Item(k).UserID = strDTSUserName;

    package.Connections.Item(k).Password = strDTSPwd;

    package.Connections.Item(k).Catalog = strDTSDBName;

    }

    if (package.Connections.Item(k).Name == "ExcelName")

    {

    package.Connections.Item(k).DataSource = strTextDataSource;

    }

    }

    #endregion

  • September 4, 2006 at 1:37 am

    #658265

    Thanks folks, I'll give that a try

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply