Not sure if this is possible (not very experienced with SQL), but we are attempting to create an SSIS package to import tables from an Access .mdb file using the Import & Export wizard (I am guessing it would be best to use SSIS directly, but unfortunately we don't have it installed on our machines). I can import the tables as they are currently, but I was hoping to 'Provide a Source Query' to allow me to bring through and rename only the columns I need, and to potentially exclude data already imported (this will be a daily import). If it is possible, how would I reference the Access tables? I set a path to the database as a whole at the start of the wizard, but cannot work out what to add before the 'from' table name.
Thanks - I think we are going to use OPENROWSET, but will need to get IT to dump a copy of the Access db onto the server as permissions issues mean it won't work in its current location (I think that, along with using the JET rather than ACE12 driver was partly what was throwing out all of my other attempts).
where is the current location? One of the user's desktop ?? If you go with the dump a copy approach then what if there are changes in the source access mdb? You will be adding one more thing you need to maintain (point of failure) so I would not recommend that.
I recommended OPENROWSET bcs you will not need SSIS and Integration Services installed on target server
We already have a longstanding daily upload of the database set up (due to a horrible Access -based lagacy system used in a few locations), so it would just be a case of dropping it in two places rather than one. It is currently on a separate server, and we get an issue similar to the one linked below, which I don't entirely trust IT to resolve (even with the resolutions in the link). I will ask them anyway, as a direct link would be preferable, but I won't hold my breath.
?? If you go with the dump a copy approach then what if there are changes in the source access mdb? You will be adding one more thing you need to maintain (point of failure) so I would not recommend that.