May 20, 2022 at 6:29 am
OK so I have a customer with a hosted website with eCommerce options. Essentially if you a good CC and the shipping address matches the address on the card, they will take your order.
This order information is stored in a MySQL database that they log into via a website, where they proceed to physically print these orders and and re-key the info into their ERP system manually. (ugly I know). This ERP system is the MS SQL 2012 I mentioned in the title.
So we already have a very nice API for the ERP system, but it only reads other MS SQL tables. So I want to use SQL Server Integration Services (SSIS) to periodically get the MySQL data and dump it into a MS SQL table. Then the API can take it from there and eliminate the manual entry.
My issue is I have never done anything like this from an external data source and a non MS SQL data source at that.
Is this doable? Has anyone else out there handled anything similar to this? I thought I would just jump in and start to try this myself but then I thought I'd ask here first. Any insights appreciated.
May 20, 2022 at 2:11 pm
The short answer is yes, you can do this in SSIS. It has a range of connection managers, and you would be able to connect to MySQL and SQL Server and use a Data Flow to migrate data across. You could, if you wanted, even use a lookup to ensure that you are only inserting new rows into the destination server (SQL Server).
I will, however, advise that SQL Server 2012 is very close to the end of support, and SSIS has changed a decent amount in more recent versions (especially with the re-introduction of single package deployments, which was previously missing in SSISDB). Ideally I would want to get onto a more recent, and fully supported version of SQL Server before exploring introducing a whole new product into your skill set.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply