none
How to create an Access database from ssis

    Question

  • Hello,

    I need to create new MS Access databases on the fly from an ssis package, then add tables to these databases.

    Any idea how I could do that?

    Thanks

    Monday, February 11, 2008 8:45 PM

Answers

  • Certain DLLs need to be copied to the %windir%\Microsoft.net\framework\v2.0.xxxxx folder in order to be accessed from Visual Studio for applications. After copying it there, you should be able to set a reference.

     

    Tuesday, February 12, 2008 12:00 AM
  • To call a COM component from managed code, you need an interop assembly.

     

    As you noted you cannot add a COM reference in VSA, the script environment in 2005, it lacks the automatic interop generation of Visual Studio proper. So with that in mind, why not create the interop assembly the old way (http://msdn2.microsoft.com/en-us/library/tw4zwhbe.aspx), and then copy it to the folder John mentioned above to ensure it is available for the VSA designer.

     

    I would supply a key file, and ensure it is signed. Tht way you can add it to the GAC which is the recommended method of dealing with such dependencies for SSIS.

     

    Tuesday, February 12, 2008 8:56 AM
  • If a Command Prompt does not obey basic commands like CD or X: to change a drive, I think you have a serious problem.

     

    You could try appending the SN folder location to the PATH environment variable for your machine. You can also qualify the path to executable and run it from anywhere.

    Tuesday, February 12, 2008 5:35 PM

All replies

  • Speaking very technical you can do anything you want with SSIS by running a command-line utility that is custom coded to create you an access database.

     

    Also knowing what i do about SSIS, it can probably then dynamically connect to this Access database...? I am not 100% sure since i have never connected to an Access database using SSIS myself.

     

    Sorry not to have a better answer for you, but i hope this helps a little.

     

    Good luck friend.

     

    Regards,

     

    Dmitry

    http://blog.lyalin.com

     

    Monday, February 11, 2008 9:54 PM
  • Actually, I can connect to an existing access db and refresh a table.

    On the Excel side, I can do that and also create files and tabs. I miss that piece for MS Access.

    I came accross a script that allows creation of Access db using ADOX however I do not know how to set the required reference, looks like you cannot refer COM objects in ssis script.

    I would rather stay away from custom command line stuff.

    Last resort, I can use an existing Access template and do a copy/rename/refresh

    Philippe
    Monday, February 11, 2008 11:28 PM
  • Certain DLLs need to be copied to the %windir%\Microsoft.net\framework\v2.0.xxxxx folder in order to be accessed from Visual Studio for applications. After copying it there, you should be able to set a reference.

     

    Tuesday, February 12, 2008 12:00 AM
  • To call a COM component from managed code, you need an interop assembly.

     

    As you noted you cannot add a COM reference in VSA, the script environment in 2005, it lacks the automatic interop generation of Visual Studio proper. So with that in mind, why not create the interop assembly the old way (http://msdn2.microsoft.com/en-us/library/tw4zwhbe.aspx), and then copy it to the folder John mentioned above to ensure it is available for the VSA designer.

     

    I would supply a key file, and ensure it is signed. Tht way you can add it to the GAC which is the recommended method of dealing with such dependencies for SSIS.

     

    Tuesday, February 12, 2008 8:56 AM
  • Darren and John, Thanks for the answer. The dll I need is msadox.dll.

    I know have to find the correct syntax to generate the key with sn.

    The specific issue I have with command line is that we are hooked to a central drive "U".

    When I try to do a cd to navigate to the directory where sn.exe is, it will not do it, it falls back immediately on the U dirve... I will not have this issue with the server, it is just for my PC. I found once an IT folk who could unstick me from the U drive so I could use command line stuff but U drive was back a day or two after.

    Any idea how I could use cd to navigate to a directory and STAY there while I use a command line prompt?

    Tuesday, February 12, 2008 3:26 PM
  • If a Command Prompt does not obey basic commands like CD or X: to change a drive, I think you have a serious problem.

     

    You could try appending the SN folder location to the PATH environment variable for your machine. You can also qualify the path to executable and run it from anywhere.

    Tuesday, February 12, 2008 5:35 PM
  • OK, Finally, I have got it.

    I am not good at command line stuff I suppose.

    I had to use C: to redir to C instead of cd C:\, then after that I could create my key, build my project with reference to the COM dll, create the interop dll assembly into the GAC and into the framework folder and build a working ssis package with reference to Interop.ADOX.

    The script in the example I mention in my second post is generating the Access database.

    For tables and columns, I use a simple execute SQL task like

    CREATE TABLE aTable  (`YR` INTEGER, `Qtr` INTEGER, `Mth` INTEGER) Using my Access connection.

    All what is left is wrap this up with variables and expressions to make it more dynamic.

    I am all set. I love SSIS. This exercise was very instructive.

    Thanks for your patience and assistance.

     

     

    Tuesday, February 12, 2008 8:34 PM