May 10, 2017 at 1:20 pm
I am looking for advice on what is the best and most efficient way to pull data out of Access, would using Access to push the data to SQL , or is using SQL to pull the data the most effective way to do it. If so would ssis be the best tool to do this?
May 10, 2017 at 1:35 pm
Yes, SSIS is the best tool for this.
Depending on your SSIS skill level , you can also use the SQL Server Import/Export wizard (which uses SSIS). You would right-click on the DB that you want to bring the data into, then pick > Tasks > Import Data... The wizard will open then, for "Source" you would pick Access. It will allow you to pick one or more tables to import. You'll have the option to configure mappings, rename columns, etc.
-- Itzik Ben-Gan 2001
May 10, 2017 at 1:47 pm
I am wanting to do this with the least hands on as possible, I have OK?? ssis skills. Also will be doing some transformation on the data when inserting it into the SQL Table.
May 10, 2017 at 2:01 pm
cbrammer1219 - Wednesday, May 10, 2017 1:47 PMI am wanting to do this with the least hands on as possible, I have OK?? ssis skills. Also will be doing some transformation on the data when inserting it into the SQL Table.
As long as you know what you're doing in SSIS, this should be pretty easy. The nice thing about SSIS is that you can run multiple imports simultaneously if you wanted which is more efficient than doing it one table at a time. The biggest "gotcha" will likely be mapping the access data to the right data types. That said, this is generally pretty easy.
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply